JOIN in SQL with Top 10 Example

Join in SQL is a concept or a mechanism. Implemented on the select statement to extract data from multiple tables. JOIN clause is used to join rows of two or more tables based on the related column between them. To extract data from multiple tables the table must have a relationship and depend on the relation join is categorized into 4 diff types.

Better to read the previous topic to get good knowledge about SQL.
Wildcard Operators in SQL
Operator in Transact SQL
Data Query Language

Before we start this topic, We need to get idea on table alias. How it works in sql query.

Working with table alias:

Table alias is a concept that is responsible to provide a duplicate name to an existing table. It is only meant for the joint statements and it is purely temporary in nature.
Please see the below example to understanding properly.

SELECT E.EMPNAME, E.DEPTID
FROM EMPLOYEES E

In the above example, We can see, E is assign for employees table name and getting table column based on table alias.
It can remove ambiguity in the select statements.

As we see on the above tables such as Employees and Departments table has one common column that is DeptID. We can join the respective employee with his\her respective department table based on deptID. Now we will write a query to join both tables.

SELECT E.*, D.* FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPTID = D.DEPTID

The above query will return the join result of both employees and departments table’s records.

Common Syntax for JOIN

SELECT TAB1.COL1,TAB1.COL2,TAB2.COL1,TAB2.COL2…………………..N
FROM TAB1 JOIN TABL2
ON TAB1.COL=TAB2.COL;

The join clause is made up of two parts:
JOIN, which tells the SQL engine the name of the table you wish to join in your query, and that you wish to use a different type of join.
ON, which tells the SQL engine what columns to use to join the two tables.

Different Types Join in SQL

INNER JOIN

It returns records that are common in both tables. This join can also be called Equi Join.
This join is the most common way to join data in SQL.

Please see the below syntax to write inner join in sql.

SELECT TABLE1.*, TABLE2.* 
FROM TABLE1
INNER JOIN TABLE2
ON TABLE1.COLUMN1 = TABLE2.COLUMN1;

Q. Write query for select employee name and employee work location.

SELECT E.ENAME, D.LOCATION FROM EMPLOYEES E 
INNER JOIN DEPARTMENTS D
ON E.DEPTID = D.DEPTID

LEFT OUTER JOIN

It returns all data from the left table and the matching records from the right table.
This means returns all the values from the left table as well as matched values from the right table. If no match could be found, LEFT JOIN returns a NULL value instead.

Please see the below syntax to write left join in sql.

SELECT TABLE1.*, TABLE2.* 
FROM TABLE1
LEFT JOIN TABLE2
ON TABLE1.COLUMN1 = TABLE2.COLUMN1;

Q. Write query for select employee name and employee work location but display all employee details on output.

SELECT E.ENAME, D.LOCATION FROM EMPLOYEES E 
LEFT JOIN DEPARTMENTS D
ON E.DEPTID = D.DEPTID

RIGHT OUTER JOIN

It retruns all data from the right table and the matching records from the left table.

Please see the below syntax to write right join in sql.

SELECT TABLE1.*, TABLE2.* 
FROM TABLE1
RIGHT JOIN TABLE2
ON TABLE1.COLUMN1 = TABLE2.COLUMN1;

Q. Write query for select employee name and employee work location but display all department details on output.

SELECT E.ENAME, D.LOCATION FROM EMPLOYEES E 
RIGHT JOIN DEPARTMENTS D
ON E.DEPTID = D.DEPTID

FULL OUTER JOIN

It returns all records when there is a match in either left or the right table.

Please see the below syntax to write full join in sql.

SELECT TABLE1.*, TABLE2.* 
FROM TABLE1
FULL JOIN TABLE2
ON TABLE1.COLUMN1 = TABLE2.COLUMN1;

Q. Write query for select employee name and employee work location but display all employee details on output.

SELECT E.ENAME, D.LOCATION FROM EMPLOYEES E 
FULL JOIN DEPARTMENTS D
ON E.DEPTID = D.DEPTID

Please look into the below image for better understanding on JOIN.

Importent Tips for JOIN in SQL

Table name prefix with the column name is optional but it becomes mandatory whenever a common attribute is phased.

Now we are covered all concept of join in sql. Now we will dicuss some advance join concept in sql.

CROSS JOIN in SQL

Result will come with cartesian product of two tables records. Left side each records will join with right side each records.

Please see the below syntax to write cross join in sql.

SELECT TABLE1.*, TABLE2.* 
FROM TABLE1
CROSS JOIN TABLE2
ON TABLE1.COLUMN1 = TABLE2.COLUMN1;

Non Equi Join in SQL

 It is a type of join that will extract data from multiple tables whenever no common attribute is available but there must be a relationship.

Please see the below example for Non EquiJoin.

The above example shows no common attribute is available in between the tables but there is a relationship between sal column of emp table and lowsal, hisal of salgrade table.

Q. Write a query which will display emp no, name, sal, grade of each employee.

SELECT EMPNO,NAME,SAL,GRADE
FROM EMP JOIN SALGRADE
ON SAL BETWEEN LOWSAL AND HISAL;

SELF JOIN in SQL

 It is a type of join where a table is joined with itself to get output. In self-join, the same table is treated as a master as well as a child.

Let’s see the below scenario to make self join in sql.

If you see the above table, You can view the one employee record to other record. In Employee details table one employee can work under manager. Manager will not excluded from employee of the company. So both recod should be present in one table.

Q. Write query to get the following result from employee details (above table) by using self join in sql.

Joshi is working under Prasad.
Trisha is working under Rakesh …
…..

SELECT W.NAME + ' IS WORKING UNDER ' + M.NAME 
FROM EMP W JOIN EMP M
ON W.MGR=M.EMPNO

As per the above query, It will not display manager records becuase condition will not satified. To get the manager records we need to use left outer join.

SELECT W.NAME + ' IS WORKING UNDER ' + M.NAME 
FROM EMP W LEFT JOIN EMP M
ON W.MGR=M.EMPNO

The above query will give you all result as well as manager’s record.

Reference to see more details join in SQL.

JOIN IN SQL SERVER
JOIN IN ORACLE

Related posts:

Operator in Transact SQL Easy to Installation of SQL Server Management Studio Group By Clause Data Query Language(DQL) OR SELECT Statement in SQL