SQL Join

 SQL join clause is used to combine two tables based on a common column between them.

      There are the following types of SQL Joins:

      1. INNER JOIN

      2. LEFT JOIN

      3. RIGHT JOIN

      4. FULL JOIN

Let's understand about each join in detail:

1. INNER JOIN:

         Inner join select those records that have matching values in both tables.

         Syntax:

               SELECT col1, col2,...colN 

               FROM table_one INNER JOIN table_two 

               ON table_one.col_name=table_two.col_name;

        Example: Consider the following Employee and Department table

Employee Table

emp_id	emp_fname	emp_lname	city	salary	dept_id
1	Anup	Mittal	UK	60000	101
2	John	Doe	Bangalore	50000	102
3	Namita	Thankur	Delhi	55000	101
4	Aman	Singh	UK	65000	103
5	Pratik	Thapar	Delhi	62000	105

Department Table

dept_id	dept_name
101	Computer Science
102	Mechanical
103	Civil
104	Electrical

        SELECT * FROM Employee INNER JOIN Department 

        ON Employee.dept_id = Department.dept_id;

Output:

emp_id	emp_fname	emp_lname	city	salary	dept_id	dept_id	dept_name
1	Anup	Mittal	UK	60000	101	101	Computer Science
2	John	Doe	Bangalore	50000	102	102	Mechanical
3	Namita	Thankur	Delhi	55000	101	101	Computer Science
4	Aman	Singh	UK	65000	103	103	Civil

        You can also select specific columns from both the tables using their table name while performing inner join as:

         SELECT table_one.col1, table_two.col2 

         FROM table_one INNER JOIN table_two 

         ON table_one.col_name = table_two.col_name;

Note: You can join multiple tables using inner join at the same time.

2. LEFT JOIN/LEFT OUTER JOIN:

         Let say we have two tables S and R then S LEFT JOIN R returns all records from S and matching records from R, if doesn't match then it will store a NULL value.

         Syntax:

              SELECT col1, col2, ..., colN 

              FROM table_one LEFT JOIN table_two 

              ON table_one.col_name= table_two.col_name;

         Example:

               SELECT * FROM Employee LEFT JOIN Department 

               ON Employee.dept_id = Department.dept_id;

emp_id	emp_fname	emp_lname	city	salary	dept_id	dept_id	dept_name
1	Anup	Mittal	UK	60000	101	101	Computer Science
2	John	Doe	Bangalore	50000	102	102	Mechanical
3	Namita	Thankur	Delhi	55000	101	101	Computer Science
4	Aman	Singh	UK	65000	103	103	Civil
5	Pratik	Thapar	Delhi	62000	105	NULL	NULL

3. RIGHT JOIN/RIGHT OUTER JOIN:

        Let say we have two tables S and R then S RIGHT JOIN R returns all records from R and matching records from S, if doesn't match then it will store a NULL value.

        Syntax:

               SELECT col1, col2, ..., colN 

               FROM table_one LEFT JOIN table_two 

               ON table_one.col_name= table_two.col_name;

         Example:

                SELECT * FROM Employee RIGHT JOIN Department 

                ON Employee.dept_id = Department.dept_id;

emp_id	emp_fname	emp_lname	city	salary	dept_id	dept_id	dept_name
1	Anup	Mittal	UK	60000	101	101	Computer Science
2	John	Doe	Bangalore	50000	102	102	Mechanical
3	Namita	Thankur	Delhi	55000	101	101	Computer Science
4	Aman	Singh	UK	65000	103	103	Civil
NULL	NULL	NULL	NULL	NULL	NULL	104	Electrical

4. FULL JOIN/FULL OUTER JOIN:

         Full outer join returns all records that matches in left or right table.

Note: FULL OUTER JOIN and FULL JOIN are same.

        Syntax:

                SELECT col1, col2, ..., colN 

                FROM table_one FULL JOIN table_two 

                ON table_one.col_name= table_two.col_name;

        Note: MySQL server does not support FULL JOIN

        

        Example:

               SELECT * FROM Employee FULL JOIN Department

               ON Employee.dept_id = Department.dept_id;

emp_id	emp_fname	emp_lname	city	salary	dept_id	dept_id	dept_name
1	Anup	Mittal	UK	60000	101	101	Computer Science
2	John	Doe	Bangalore	50000	102	102	Mechanical
3	Namita	Thakur	Delhi	55000	101	101	Computer Science
4	Aman	Singh	UK	65000	103	103	Civil
5	Pratik	Thapar	Delhi	62000	105	NULL	NULL
6	NULL	NULL	NULL	NULL	NULL	104	Electrical

Continue Learning: SQL LIKE Operator