The SQL LEFT JOIN joins two tables based on a common column, and selects records that have matching values in these columns and remaining rows from the left table.
Example
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
LEFT JOIN Orders
ON Customers.customer_id = Orders.customer;
Here's how this code works:
Here, the SQL command selects customer_id and first_name columns (from the Customers table) and the amount column (from the Orders table).
And, the result set will contain those rows where there is a match between customer_id (of the Customers table) and customer (of the Orders table) along with all the remaining rows from the Customers table.
Syntax of LEFT JOIN
The syntax of LEFT JOIN is:
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
LEFT JOIN With WHERE Clause
The SQL command can have an optional WHERE clause with the LEFT JOIN statement. For example,
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
LEFT JOIN Orders
ON Customers.customer_id = Orders.customer
WHERE Orders.amount >= 500;
Here, the SQL command joins two tables and selects rows where the amount is greater than or equal to 500.
SQL LEFT JOIN With AS Alias
We can use AS aliases inside LEFT JOIN to make our snippet short and clean. For example,
SELECT C.cat_name, P.prod_title
FROM Categories1 AS C
LEFT JOIN Products AS P
ON C.cat_id= P.cat_id;
Here, the SQL command selects common rows between Category and Products table.
Left Join Vs Other Joins
We can also use LEFT OUTER JOIN instead of LEFT JOIN. Basically, these two clauses are the same.
That means,
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
LEFT JOIN Orders
ON Customers.customer_id = Orders.customer;
is similar to
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.customer_id = Orders.customer;
The LEFT JOIN selects the common rows as well as all the remaining rows from the left table. Whereas the INNER JOIN selects only the common rows between two tables.
Let's take a look at example,
LEFT JOIN
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
LEFT JOIN Orders
ON Customers.customer_id = Orders.customer;
Output
INNER JOIN
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
INNER JOIN Orders
ON Customers.customer_id = Orders.customer;
Output
The LEFT JOIN selects the common rows as well as all the remaining rows from the left table. Whereas the RIGHT JOIN selects the common rows as well as all the remaining rows from the right table.
Let's take a look at example,
LEFT JOIN
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
LEFT JOIN Orders
ON Customers.customer_id = Orders.customer;
Output
RIGHT JOIN
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
RIGHT JOIN Orders
ON Customers.customer_id = Orders.customer;
Output
The LEFT JOIN selects the common rows as well as all the remaining rows from the left table. Whereas the FULL OUTER JOIN selects all the rows from both the tables.
Let's take a look at example,
LEFT JOIN
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
LEFT JOIN Orders
ON Customers.customer_id = Orders.customer;
Output
FULL OUTER JOIN
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
FULL OUTER JOIN Orders
ON Customers.customer_id = Orders.customer;
Output
Recommended Readings