Combine Two Tables

In a LEFT JOIN operation, the “left” table refers to the table that appears to the left of the JOIN keyword in the SQL query, and the “right” table refers to the one that appears to the right.

In the given query:

1
2
3
4
5
6

SELECT p.firstName,
       p.lastName,
       a.city,
       a.state
FROM Person p
LEFT JOIN Address a ON p.personId = a.personId;

The Person table (aliased as p) appears to the left of the JOIN keyword, so it is considered the “left” table. Conversely, the Address table (aliased as a) appears to the right of the JOIN keyword, so it’s considered the “right” table.

A LEFT JOIN returns all the records from the left table along with the matching records from the right table. If there is no match, the result will have NULL for all the columns of the right table. Therefore, choosing the Person table as the left table ensures that all persons are included in the result, even if they don’t have a corresponding entry in the Address table.

So the left join will return all columns in the left table, even when there is no records on the right table that matches?

Yes, exactly! A LEFT JOIN returns all the records from the left table, and if there are no matching records in the right table, the columns corresponding to the right table will be filled with NULL values.

In the given context, every person from the Person table will appear in the result. If a person doesn’t have a corresponding entry in the Address table, the city and state columns for that person will be NULL in the result.