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.
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.
People are also reading: