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.

    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: