SQL Joins

SQL Joins

This tutorial covers SQL Joins.

Joins perform retrieval of data from two or more tables based on certain connection (or relationship).

In other words, a join statement merge two or more tables on the basis of a specific column. Most commonly, the specific column refers to a foreign key column in one table and its related key in the other table.

This page encompasses:

• inner join
• left outer join
• right outer join
• full outer join
• cross join

Here we use two tables. First is the table “student_info” from the previous tutorials, and the second is a table named “grade“, linked to the first table with a foreign key.

id	firstName	lastName	age
1	John	Smith	21
2	Patrick	Casey	26
3	Sam	Adams	19
4	Mike	Garcia	NULL
5	Patrick	Dumont	21

id	subjectName	grade	passed	studentID
1	History	C	1	1
2	Physics	A	1	2
3	History	F	0	3
4	Biology	A	1	NULL
5	Biology	E	1	5

Inner Join

The “inner join” statement retrieves all data that matches in all tables.

Below is an example.

SELECT firstName, age FROM student_info
INNER JOIN grade ON student_info.id = grade.studentID; 

firstName	age
John	21
Patrick	26
Sam	19
Patrick	21

The above query selects the first name and age of all students that match the “id” of the first table with the “studentID” column in the second table.

4th student id does not match in both tables, hence not displayed in the output.

Left Join

The “left join” statement retrieves all data from the first table as well as all data that matches in all tables.

SELECT firstName, age FROM student_info
LEFT JOIN grade ON student_info.id = grade.studentID; 

firstName	age
John	21
Patrick	26
Sam	19
Mike	NULL
Patrick	21

The above query selects the first name and age of all students in the first table. And any relevant data matching the “id” column of the first table with the “studentID” column in the second table.

Although 4th student id does not match in both tables, the query retrieves all data from the first table’s id column.

Right Join

The “right join” statement retrieves all data from the second table as well as all data that matches in all tables.

SELECT firstName, age FROM student_info
RIGHT JOIN grade ON student_info.id = grade.studentID; 

firstName	age
John	21
Patrick	26
Sam	19
Mike	NULL
Patrick	21

The above query selects the first name and age of all students in the second table. And any relevant data matching the “id” column of the first table with the “studentID” column in the first table.

Although 4th student id does not match in both tables, the query retrieves all data from the second table’s id column.

Full Join

The “full join” statement retrieves all data from the first and second table, whether matching or not.

SELECT firstName, age FROM student_info
FULL JOIN grade ON student_info.id = grade.studentID; 

firstName	age
John	21
Patrick	26
Sam	19
Mike	NULL
Patrick	21
NULL	NULL

The above query selects the first name and age of all students in both, the first and second table. It also includes any matching or otherwise relevant data.

The 6th record row is the unmatched data from the 4th student id. The record cell under the first name column is also NULL as it is empty.

Cross Join

The “cross join” statement retrieves the Cartesian product of the data.

The Cartesian product is the product of two sets. In other words, each row from the first table is combined with each row from the second table.

SELECT student_info.firstName, grade.grade
FROM student_info
CROSS JOIN grade;

Cross joins can produce large results. For this reason, we will limit the output to 10 records with the “select top” statement.

SELECT TOP 10 student_info.firstName, grade.grade
FROM student_info
CROSS JOIN grade;

firstName	grade
John	C
Patrick	C
Sam	C
Mike	C
Patrick	C
John	A
Patrick	A
Sam	A
Mike	A
Patrick	A

The above query produces the Cartesian product of the two selections.

Each table has 5 records. This creates the Cartesian product outcome of 25 records (5 x 5).

---

Next: SQL Case