SQL JOINs

Types of JOINs

[INNER] JOIN

For each record from the left table, all records from the right table that satisfy the merge condition will be merged with the original record and added to the resulting table. If no matching records are found in the right table, the original record is also not added to the resulting table (using the INNER keyword is optional).

LEFT/RIGHT JOIN

Returns all the records from the left/right table and the matching values from the right/left table. If some records in one of the tables (for example, the left one) do not match the other (for example, the right one) table, the corresponding columns in the output of this right table will be assigned NULL.

FULL [OUTER] JOIN

Returns all records when a match is in the left or the right table. Records that do not have a match are assigned NULL (using OUTER is optional).

CROSS JOIN

Joins up every record from the left table and every record in the right table (the algorithm is illustrated in the picture below).

So id1 from table1 gets joined with idA, idB, and idC, id2 also gets joined with idA, idB, and idC, and the same holds for id3.

INNER JOIN

To see how different types of JOINs work, let's consider the following tables: character, pet, and universe.

The character table has columns named character_id (INT), character_name (VARCHAR(30)), and universe_id (INT):

The pet table has columns named character_id (INT) and pet_species (VARCHAR(50)):

The universe table has columns named universe_id (INT), universe_name (VARCHAR(30)), and author (VARCHAR(30)):

The common syntax for INNER JOIN is the following:

SELECT table1.col_name1, table2.col_name2
FROM table1 [AS a]
INNER JOIN table2 [AS b]
    ON table1.id = table2.id;

The join condition can be some other than equality. The commands in brackets are optional.

Ordering results of INNER JOIN

To find out what pet belongs to what character from the previous example, execute the following query:

SELECT character_name, pet_species 
FROM character 
INNER JOIN pet
    ON character.character_id = pet.character_id
ORDER BY character.character_name;

The resulting table from this query will be the following:

Not all characters were included in the result due to some records in the left table not having matches in the right table. In the example, not all characters had pets. To fix this problem and include all the characters in the resulting table, use LEFT JOIN. Combining information from two tables by matching rows where character_id was the same in the two tables.

SELECT character_name, pet_species 
FROM character 
LEFT JOIN pet
    ON character.character_id = pet.character_id
ORDER BY character.character_name;

You can join multiple tables. For example, one can want to know which universe our characters come from. Values that didn't have matches in the right table were assigned with NULL values.

SELECT c.character_name, p.pet_species, u.universe_name 
FROM universe AS u
LEFT JOIN character AS c
    ON u.universe_id = c.universe_id
LEFT JOIN pet AS p
    ON p.character_id = c.character_id

To understand how CROSS JOIN maybe useful, think of the chess competitions where organizers must pair up all the participants. Also, note that when selecting columns, it is not mandatory to write table_name1.col_name. If the column name is unique for all tables, omit it. However, it is still recommended to specify it because the names of the columns become ambiguous when dealing with multiple tables.

Use aliases for table names. SQL aliases give a table (or a column in a table) a temporary name. For example, SELECT person_name AS name. They are beneficial if tables have long names.

It is not mandatory to use JOIN if you want to connect tables. Use the WHERE condition to get the same result. The script below would give an identical result to the one above:

SELECT table1.col_name1, table2.col_name2
FROM table1 [AS a], table2 [AS b]
    WHERE table1.id = table2.id;

The commands in brackets are optional.

To join multiple tables, use this general template:

SELECT val1 [AS name1], ..., valN [AS nameN]
FROM table1 
[type_of_join] JOIN  table2 
    ON table1.col_name_table1 = table2.col_name_table2
[type_of_join] JOIN table3
    ON table2.col_name_table2 = table3.col_name_table3;