SQL JOIN – CROSS, INNER, LEFT, RIGHT joins with Examples

SQL JOIN

SQL Join is one of the most frequently asked topics asked in interviews.

Now a days, database operations are very common while dealing with data storage across web/mobile apps.

Either to query a database stored in local(like SQLite) or a remote database (like MySQL), SQL operations help us retrieving the data easily.

SQL Join, in particular, helps to retrieve data from one or more tables in a Relational database based on common column/columns between them.

In this tutorial let’s discuss about various MySQL & SQLite Join statements.

For ease of understanding , consider below sample database with Employees & Departments tables.

Employees Table:

Emp_idEmp_nameEmp_RoleEmp_Hire_dateDept_id
1JohnAdmin2000-02-021
2SmithManager2016-01-023
3AbrahamEngineer2018-01-022
4GracieCEO2000-01-021
5EliasTrainee<null>3
6MarieOperations2018-07-204

Departments Table:

Dept_idDept_nameLocation
1HRNewyork
2SalesSunrise
3FinanceFlorida
4Operations<null>

Different types of Joins supported by  MySQL Relational database are:

  • CROSS JOIN
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN

Note: MySQL does not support Full Outer Join.

Different types of Joins supported by  SQLite Relational database are:

  • CROSS JOIN
  • INNER JOIN
  • LEFT JOIN

Note: SQLite does not support Right Outer Join & Full Outer Join.

CROSS JOIN: 

Cross Join matches every row from first database table to every row of another database table.CROSS_JOINTo Perform this SQL join, CROSS JOIN Clause is used as below:

MySQL Syntax:

SQLite Syntax:

Resultant Output:

Emp_idEmp_nameDept_name
1JohnHR
1JohnSales
1JohnFinance
1JohnOperations
2SmithHR
2SmithSales
2SmithFinance
2SmithOperations
3AbrahamHR
3AbrahamSales
3AbrahamFinance
3AbrahamOperations
4GracieHR
4GracieSales
4GracieFinance
4GracieOperations
5EliasHR
5EliasSales
5EliasFinance
5EliasOperations
6MarieHR
6MarieSales
6MarieFinance
6MarieOperations

Note: WHERE Clause is not specified in CROSS JOIN. By Specifying WHERE Clause, this kind of  SQL Join will function as INNER JOIN.

INNER JOIN:

Inner Join produces rows from two joined tables with common values which satisfy the given condition with non-NULL values.  INNER_JOINTo Perform this SQL join, INNER JOIN Clause is used as below:

MySQL Syntax:

SQLite Syntax:

Resultant Output:

Emp_idEmp_nameDept_name
1JohnHR
2SmithFinance
3AbrahamSales
4GracieHR
5EliasFinance
5MarieOperations

LEFT JOIN:  

As Inner Join, Left Join also requires a condition need to be satisfied. Left Join returns all the rows of Left table even if there are no matching rows found in the right table. NULL is returned when no matches found in the right table.LEFT_JOINTo Perform this SQL join, LEFT JOIN Clause is used as below:

MySQL Syntax:

SQLite Syntax:

Resultant Output:

Emp_idEmp_nameEmp_Hire_dateDept_name
1John2000-02-02HR
2Smith2016-01-02Finance
3Abraham2018-01-02Sales
4Gracie2000-01-02HR
5Elias<null>Finance
6Marie2018-07-20Operations

RIGHT JOIN: 

RIGHT JOIN is exactly the opposite of LEFT JOIN. Right Join returns all the rows of Right table even if there are no matching rows found in the left table. NULL is returned when no matches found in the left table.SQL JOIN-RIGHT_JOINTo Perform this SQL join, RIGHT JOIN Clause is used as below:

MySQL Syntax:

SQLite Syntax:

SQLite does not support Right Outer Join.

Resultant Output:

Emp_idEmp_nameEmp_RoleDept_idDept_nameLocation
1JohnAdmin1HRNewyork
2SmithManager3FinanceFlorida
3AbrahamEngineer2SalesSunrise
4GracieCEO1HRNewyork
5EliasTrainee3FinanceFlorida
6MarieOperations4Operations