What is the difference between INNER JOIN and LEFT JOIN in a MySQL query when trying to fetch data from multiple tables?

When fetching data from multiple tables in a MySQL query, the main difference between INNER JOIN and LEFT JOIN is how they handle unmatched rows. INNER JOIN only returns rows that have matching values in both tables, while LEFT JOIN returns all rows from the left table and the matched rows from the right table (if any). If there are no matches in the right table, LEFT JOIN will still return all rows from the left table with NULL values for columns from the right table. 

<?php
// Connect to the database
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";
$conn = new mysqli($servername, $username, $password, $dbname);

// Query using INNER JOIN
$sql = "SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id";
$result = $conn->query($sql);

// Query using LEFT JOIN
$sql = "SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id";
$result = $conn->query($sql);

// Fetch data and display
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "Column1: " . $row["column1"]. " - Column2: " . $row["column2"]. "<br>";
    }
} else {
    echo "0 results";
}

// Close connection
$conn->close();
?>

Keywords

INNER JOIN LEFT JOIN MySQL query fetch data multiple tables

Related Questions