8.17. SQL Join

8.17.1. Rationale

The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each. 1

../_images/sql-joins.png
../_images/sql-join-clause.png
../_images/sql-join-constraint.png
../_images/sql-join-operator.png

8.17.2. INNER JOIN

  • Returns rows when there is a match in both tables

  • The most important and frequently used of the joins

The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row. 7

SELECT *
FROM astronauts
INNER JOIN mission
ON astronauts.id = mission.astronaut_id;
../_images/sql-innerjoin.gif

8.17.3. LEFT JOIN

  • Returns all rows from the left table, even if there are no matches in the right table

This means that if the ON clause matches 0 (zero) records in the right table; the join will still return a row in the result, but with NULL in each column from the right table. 2

This means that a left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate. 2

SELECT *
FROM astronauts
LEFT JOIN mission
ON astronauts.id = mission.astronaut_id;
../_images/sql-leftjoin.gif

8.17.4. RIGHT JOIN

  • Returns all rows from the right table, even if there are no matches in the left table

This means that if the ON clause matches 0 (zero) records in the left table; the join will still return a row in the result, but with NULL in each column from the left table. 3

This means that a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate. 3

SELECT *
FROM astronauts
RIGHT JOIN mission
ON astronauts.id = mission.astronaut_id;
../_images/sql-rightjoin.gif

8.17.5. FULL JOIN

  • Combines the results of both left and right outer joins

  • The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side. 4

SELECT *
FROM astronauts
INNER JOIN mission
ON astronauts.id = mission.astronaut_id;
../_images/sql-fulljoin.gif

8.17.6. OUTER JOIN

SELECT *
FROM astronauts
FULL OUTER JOIN mission
ON astronauts.id = mission.astronaut_id;

8.17.7. SELF JOIN

  • Is used to join a table to itself as if the table were two tables

  • Temporarily renaming at least one table in the SQL statement

SELECT *
FROM astronauts Astro1,
     astronauts Astro2,
WHERE Astro1.id != Astro2.id
AND Astro1.agency = Astro2.agency
ORDER BY Astro1.agency;

8.17.8. CARTESIAN JOIN

  • Also known as CROSS JOIN

  • Returns the Cartesian product of the sets of records from the two or more joined tables.

Thus, it equates to an inner join where the join-condition always evaluates to either True or where the join-condition is absent from the statement. 6

SELECT astronauts.firstname,
       astronauts.lastname,
       missions.name,
       missions.year
FROM astronauts, missions;

8.17.9. References

1

https://www.tutorialspoint.com/sql/sql-using-joins.htm

2(1,2)

https://www.tutorialspoint.com/sql/sql-left-joins.htm

3(1,2)

https://www.tutorialspoint.com/sql/sql-right-joins.htm

4

https://www.tutorialspoint.com/sql/sql-full-joins.htm

5

https://www.tutorialspoint.com/sql/sql-self-joins.htm

6

https://www.tutorialspoint.com/sql/sql-cartesian-joins.htm

7

https://www.tutorialspoint.com/sql/sql-inner-joins.htm