SQL JOINS

By | September 13, 2020
SQL JOINS

JOIN is one of the most powerful and used commands in SQL, JOINS are used to combine rows from two or more tables from the same database and while we join the tables there should be a relation between both the tables.

We use JOIN when we want to fetch data from two tables and wants to see the data relation between the tables, and we can only perform the JOIN operations when there is one-to-many or many-to-many relationship between the tables.

Vamware

SQL JOINS

The JOIN command only creates a temporary table showing the data from the joined tables.

For instance, if there is a table of students which hold the students’ details and there is another table library which holds the details of books taken by the students. What if using the library table we want to see the student details who has just borrowed the book from the library, it would be unnecessary to have similar data values in two tables this increase Data redundancy and occupy extra space in the memory. To solve this problem we use the JOIN command which will JOIN the tables library and students and show the student details along with its library details.

Syntax

SELECT Table_name_1.coulum_name_1, Table_name_2.column_name_1.....
FROM Table_name_1 JOIN Table_name_2 ON [Condition]

Example

For the example considered these two tables:

students table

+------+--------+------+--------+-------+----------+
| id   | name   | age  | grades | marks | Trade    |
+------+--------+------+--------+-------+----------+
|    1 | Luffy  |   16 | A      |   970 | Science  |
|    2 | Naruto |   18 | A      |   960 | Humanity |
|    3 | Zoro   |   20 | A      |   940 | Commerce |
|    4 | Sanji  |   21 | B      |   899 | Humanity |
|    5 | Nami   |   17 | B      |   896 | Science  |
|    6 | Robin  | NULL | B      |   860 | Humanity |
|    7 | Goku   |   27 | B      |   860 | Humanity |
+------+--------+------+--------+-------+----------+

Library Table

+---------+-------------------+------------+
| Book_ID | Book_Name         | Student_ID |
+---------+-------------------+------------+
|    1124 | One Hundred years |          1 |
|    1104 | The Great Escape  |          2 |
|    1209 | Beloved           |          6 |
|    1111 | Hollow            |          4 |
|    2351 | Invisibal Man     |          3 |
|    1034 | A Passage         |          2 |
|    1211 | Hero              |          6 |
|    1188 | Your Name         |          5 |
|    1211 | Hero              |          6 |
|    1000 | My Hope           |          8 |
|    1000 | Go Away           |         10 |
+---------+-------------------+------------+

Here you can see that The students table (ID) has a one-to-many relation with the library table (Student_ID).

Query: Show the students name, their trade and all the books which they have borrowed from the library.

SELECT students.name, students.trade, library.Book_Name
FROM students JOIN Library
ON students.id = library.Student_ID
ORDER BY students.name ASC;

Output

+--------+----------+-------------------+
| name   | trade    | Book_Name         |
+--------+----------+-------------------+
| Luffy  | Science  | One Hundred years |
| Nami   | Science  | Your Name         |
| Naruto | Humanity | The Great Escape  |
| Naruto | Humanity | A Passage         |
| Robin  | Humanity | Beloved           |
| Robin  | Humanity | Hero              |
| Robin  | Humanity | Hero              |
| Sanji  | Humanity | Hollow            |
| Zoro   | Commerce | Invisibal Man     |
+--------+----------+-------------------+

Types of JOINS

There are 4 major types of JOINs in SQL:

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN

1. INNER JOIN

The INNER JOIN is similar to the simple JOIN command, it returns those data records which have matching values on both the tables. Here, with the ON clause, we set a condition which acts as a reference or the base criteria to floor the matching process.

Syntax

SELECT Table1.Col_name_1,Table1.Col_name_2,Table2.Col_name_1.....
FROM Table1
INNER JOIN Table2
ON Table1.base_column = Table2.base_column

Example

Query: Perform the INNER JOIN on students and library table:

SELECT students.id, students.name, students.trade, library.Book_Name
FROM students INNER JOIN Library
ON students.id = library.Student_ID
ORDER BY students.ID;

Output

+------+--------+----------+-------------------+
| id   | name   | trade    | Book_Name         |
+------+--------+----------+-------------------+
|    1 | Luffy  | Science  | One Hundred years |
|    2 | Naruto | Humanity | A Passage         |
|    2 | Naruto | Humanity | The Great Escape  |
|    3 | Zoro   | Commerce | Invisible Man     |
|    4 | Sanji  | Humanity | Hollow            |
|    5 | Nami   | Science  | Your Name         |
|    6 | Robin  | Humanity | Hero              |
|    6 | Robin  | Humanity | Hero              |
|    6 | Robin  | Humanity | Beloved           |
+------+--------+----------+-------------------+

In this output, you can see that we do not have any result for Goku because Goku has no matching Student_ID in library, which means Goku has not borrowed any book from the library.

2. Left Join

The LEFT JOIN or LEFT outer JOIN show all the data records from the left table and the matching record from the right table. If there is no value matching in the right table then the output of that recode will be a NULL value.

Syntax

SELECT Table1.Col_name_1,Table1.Col_name_2,Table2.Col_name_1.....
FROM Table1
LEFT JOIN Table2
ON Table1.base_column = Table2.base_column

Example

Query: Perform the LEFT JOIN on students and library table:

SELECT students.id, students.name, students.trade, library.Book_Name
FROM students LEFT JOIN Library
ON students.id = library.Student_ID
ORDER BY students.ID;

Output

+------+--------+----------+-------------------+
| id   | name   | trade    | Book_Name         |
+------+--------+----------+-------------------+
|    1 | Luffy  | Science  | One Hundred years |
|    2 | Naruto | Humanity | A Passage         |
|    2 | Naruto | Humanity | The Great Escape  |
|    3 | Zoro   | Commerce | Invisibal Man     |
|    4 | Sanji  | Humanity | Hollow            |
|    5 | Nami   | Science  | Your Name         |
|    6 | Robin  | Humanity | Hero              |
|    6 | Robin  | Humanity | Hero              |
|    6 | Robin  | Humanity | Beloved           |
|    7 | Goku   | Humanity | NULL              |
+------+--------+----------+-------------------+

3. Right Join

It is the opposite just the of LEFT JOIN, the RIGHT JOIN or RIGHT outer JOIN show all the data records from the right table and the matching record from the left table. If there is no value matching in the left table then the output of that recode will be a NULL value.

Syntax

SELECT Table1.Col_name_1,Table1.Col_name_2,Table2.Col_name_1.....
FROM Table1
RIGHT JOIN Table2
ON Table1.base_column = Table2.base_column

Example

Query: Perform the RIGHT JOIN on students and library table:

SELECT  students.name, students.trade, library.Book_Name
FROM students RIGHT JOIN Library
ON students.id = library.Student_ID
ORDER BY students.ID;

Output

+--------+----------+-------------------+
| name   | trade    | Book_Name         |
+--------+----------+-------------------+
| NULL   | NULL     | My Hope           |
| NULL   | NULL     | Go Away           |
| Luffy  | Science  | One Hundred years |
| Naruto | Humanity | The Great Escape  |
| Naruto | Humanity | A Passage         |
| Zoro   | Commerce | Invisibal Man     |
| Sanji  | Humanity | Hollow            |
| Nami   | Science  | Your Name         |
| Robin  | Humanity | Beloved           |
| Robin  | Humanity | Hero              |
| Robin  | Humanity | Hero              |
+--------+----------+-------------------+

4. Full JOIN

The FULL JOIN returns all records when there is a match in either left or right table

Syntax

SELECT Table1.Col_name_1,Table1.Col_name_2,Table2.Col_name_1.....
FROM Table1
RIGHT JOIN Table2
ON Table1.base_column = Table2.base_column

Example

Query: Perform the FULL JOIN on students and library table:

SELECT students.id, students.name, students.trade, library.Book_Name
FROM students FULL JOIN Library
ON students.id = library.Student_ID
ORDER BY students.ID;

Note: FULL JOIN DOES not work on MySQL, so for MySQL, we can perform the union operation between the LEFT and RIGHT JOIN which is equivalent to the FULL JOIN:

My SQL FULL JOIN Equivalent using UNION:

SELECT students.name, students.trade, library.Book_Name 
FROM students RIGHT JOIN Library 
ON students.id = library.Student_ID
UNION
SELECT students.name, students.trade, library.Book_Name 
FROM students LEFT JOIN Library 
ON students.id = library.Student_ID;

Output

+--------+----------+-------------------+
| name   | trade    | Book_Name         |
+--------+----------+-------------------+
| Luffy  | Science  | One Hundred years |
| Naruto | Humanity | The Great Escape  |
| Naruto | Humanity | A Passage         |
| Zoro   | Commerce | Invisible Man     |
| Sanji  | Humanity | Hollow            |
| Nami   | Science  | Your Name         |
| Robin  | Humanity | Beloved           |
| Robin  | Humanity | Hero              |
| NULL   | NULL     | My Hope           |
| NULL   | NULL     | Go Away           |
| Goku   | Humanity | NULL              |
+--------+----------+-------------------+

Summary

  • The JOIN clause returns a temporary table by joining two tables where there is a matching value for both the tables.
  • There are four major types of JOINS INNER, RIGHT, LEFT and FULL
  • MySQL does not support FULL JOIN so for that we use the UNION operator between the LEFT and RIGHT Joined tables.

Leave a Reply

Your email address will not be published. Required fields are marked *