JOINs

References:
https://www.sqltutorial.org/sql-inner-join/
https://www.sqltutorial.org/sql-left-join/

INNER JOIN

Inner join that returns rows if there is at least, one row in both tables, that matches the join condition. The inner join clause eliminates the rows that do not match with a row of the other table.

Suppose, you have two tables: A and B.

Table A has four rows: (1,2,3,4) and table B has four rows: (3,4,5,6)

When table A joins with table B using the inner join, you have the result set (3,4) that is the intersection of table A and table B.

 

LEFT JOIN

Left join returns all rows from the left table whether or not there is a matching row in the right table (the result is everything in the left table and any matches in the right table).

Suppose we have two tables A and B. The table A has four rows 1, 2, 3 and 4. The table B also has four rows 3, 4, 5, 6.

When we join table A with table B, all the rows in table A (the left table) are included in the result set whether there is a matching row in the table B or not (5 and 6 from table B are not included).

Left joins will result in null column values if a row exists in table A and not table B, the columns which are comming from table B will have null values. This is different to inner joins. Because non-matching rows in the right table are filled with the NULL values, you can apply the LEFT JOIN clause to miss-match rows between tables.

 

# Select all countries and all cities.
# All rows are selected from the table in the FROM clause (this is the "left" table).
# Only rows from cities table are chosen if they have a matching country ID to one of the rows in the countries table (this is the "right" table).
# Because this is a LEFT JOIN, this means that if there are no cities in a specific country,
# there will be a row with a country name and null city name:
#
SELECT
    countries.name,
    cities.name,
FROM
    countries
LEFT JOIN cities ON cities.country_id = country.id;

# If a WHERE clause is used, the WHERE applies to the countries table,
# the LEFT JOIN then joins the remaining rows from the countries table with the cities table:
#
SELECT
    countries.name,
    cities.name,
FROM
    countries
LEFT JOIN cities ON cities.country_id = country.id
WHERE
    countries.id in (1,2,3);

 

---