SQL Joins

SQL Joins: Joins in SQL Easy 4 Examples

by

Description

In This Tutorial, we will see examples of SQL Joins. Learn how to use SQL Joins with examples. Understand different types of SQL joins and join multiple tables for data retrieval.

SQL Joins are required for fetching rows or data from different tables in Relational database management system.

SQL Joins are used to combine multiple tables using columns based on same related column from another table.

SQL Joins

Table of Contents

What is SQL Joins

SQL Joins are required for fetching rows or data from different tables in Relational database management system.
Joins are used to combine multiple tables using columns based on same related column from another table.

If you want to fetch data from many tables you must have to use Join for combine two or more tables togather and your table must have the relational columns in all the tables which you want to join with each other to form data.

In this tutorial we will understand different types of SQL Joins with example.

Types of SQL Joins:

1.Inner Join

2.Left Join

3.Right Join

4.Full Join

Let’s us See All SQL Joins with example one by one.

Inner Join

Inner join fetch all data from both the tables, if there is column data mach based on defined condition.

Suppose you have table ‘EmpMaster’ and Table ‘Employee’ and you have to fetch two column ‘EmployeeDOB’ and ‘Name’ and ‘EmployeeDOB’ column is in the table Employee and ‘Name’ is stored in the table EmpMaster then you can write SQL Query with Inner Join like

Inner Join Example 


Select 
EmpMaster.Name,
EmpMaster.Email,
Employee.EmployeeSalary,
Employee.EmployeeDOB 
from EmpMaster INNER JOIN Employee on
EmpMaster.ID=Employee.EmployeeID;

Or you can Write an SQL Query To Join Two Tables

select 
EmpMaster.Name,
EmpMaster.Email,
Employee.EmployeeSalary,
Employee.EmployeeDOB 
from EmpMaster , Employee WHERE
EmpMaster.ID=Employee.EmployeeID

SQL Join Using Alias

SQL Aliases | Table and Column Aliases


select 
A.Name,
A.Email,
B.EmployeeSalary,
B.EmployeeDOB 
from EmpMaster A , Employee B WHERE
A.ID=B.EmployeeID

In this example we retrieve Name AND Email, data from EmpMaster table and EmployeeSalary AND
EmployeeDOB from Employee table where there is match ‘ID’ FROM EmpMaster with EmployeeID from Employee tables.

Left Join (or Left Outer Join):

Left Join in SQL server returns all rows and columns from the first table (data will be fetched from Left Table from your SQL query)
and match the rows from the Right-side table or second table if you have two tables in your SQL query.
if the row is not matched with right side table the data from right side table will shows null values.
Left Join Can be used to retrieve all data from left side table.
If you want to fetch the records based on specific table, then you can put your table in left and use Left Join in SQL server.

Left join can fetch all records from first table or left table and matching column records from the second table right table, if there is no record matched it will return null values for right table records.

https://itdevelopement.com/

Left Join Example:

select 
A.Name,
A.Email,
B.EmployeeSalary,
B.EmployeeDOB 
from EmpMaster A LEFT JOIN Employee B on
A.ID=B.EmployeeID

In this left join query EmpMaster table will fetch all records and Employee table will fetch matched Id records with EmpMaster table and, if ID is not match it will return null values for Employee table.
We are joining EmpMaster table with Employee table based on ID relational column.

Right Join (or Right Outer Join):

A right join fetch all rows from the First (right) table and the matched rows from the Second (left) table.
If there’s no match, NULL values are returned for the columns from the left table.

Right join works exactly opposite to left join or Left Outer Join.
Right Join in SQL will return or fetch all records or data from right (Second) table and mapped data from left (first) table. If there is no match in the left table, it will return null values.


Right Join Can be used to retrieve all data from right side table (Second Table).
In this Join if you need to fetch all data from the right-side table then you can use Right Join in SQL server.

Right Join Example:

select 
A.Name,
A.Email,
B.EmployeeSalary,
B.EmployeeDOB 
from EmpMaster A RIGHT JOIN Employee B on
A.ID=B.EmployeeID;

In this RIght join query Employee table will fetch all records and EmpMaster table will fetch matched Id records with Employee table and, if ID is not match it will return null values for EmpMaster table

Right Join returns all data from the right table and matching data from left table, if id is not matched with right table, then left table will return null values.

Full Join (or Full Outer Join):

Full Join is combination of Left Join and Right Join, it will return all rows from left table and all rows from left table. Even if there are no matches the Id.


Full Join can fetch all rows from all the tables, Full Join in SQL returns all information present in the table in the form of rows and columns, even if there is no match in right table. This will return all rows from both Left and Right table.

Full Join Example:

select 
A.Name,
A.Email,
B.EmployeeSalary,
B.EmployeeDOB 
from EmpMaster A FULL JOIN Employee B on
A.ID=B.EmployeeID

This Query will return all rows present in the left table and all present in the left table without mapping of Id column in both the table.
Here Address, Column1, Column2 Column_N return from Left table EmpMaster and Name, COlumn2, column3, column_N from Employee right table, Full Join does not validate any tables relational column to return specific records. It will return all records if there is match or no match with ID.

Join Two or more Table in SQL

We can Join More than two tables in SQL server by using Different SQL joins like Simple Join, Inner Join, Left Join, Right Join and Full Join or Left Outer, Right Outer, Full Outer Joins.

Let’s See an Example of Joining multiple tables in SQL, below is the sample query to join more than two tables in SQL server.

Join Multiple Table Example:

select 
A.Name,
A.Email,
B.EmployeeSalary,
B.EmployeeDOB 
from EmpMaster A , Employee B , Employee_1 C where
A.ID=B.EmployeeID
 and B.EmployeeID=C.EmployeeID;

OR 

select 
A.Name,
A.Email,
B.EmployeeSalary,
B.EmployeeDOB 
from EmpMaster A , 
Employee B LEFT JOIN Employee_1 C 
on B.EmployeeID=C.EmployeeID
where
A.ID=B.EmployeeID;

Conclusion:

SQL Joins are powerful data manipulations tools which used to retrieve different tables data with joining tables with each other by matching values. Different joins can be used to combine different tables.

FAQ on SQL Joins

Q. Can we join More than two tables in SQL
Ans: Yes, we can join Multiple Tables with each other in SQL server using different types of SQL Joins.

Q. What is the purpose of SQL joins?
Ans: SQL Join is used to attach different tables with each other based on relational columns. when you want a data from different tables then you can use SQL joins in relational database.

Q. What are the different types of SQL joins?
Ans:
1.INNER JOIN
2.LEFT JOIN (or LEFT OUTER JOIN)
3.RIGHT JOIN (or RIGHT OUTER JOIN)
4.FULL JOIN (or FULL OUTER JOIN)
5.CROSS JOIN

Q. Difference between INNER and OUTER JOIN in SQL?

Ans: Inner Join rerives data from both tables only for matched columns and Outer join retive the data from both the table ,with null values if column not match.

Q. When should I use an INNER JOIN?

Ans: If You want only matching values records from the table then you can use Inner join, Inner join retrieves data for matched values.

Q. Can you provide an example of a LEFT JOIN?

Ans :

select
A.Name,
A.Email,
B.EmployeeSalary,
B.EmployeeDOB
from EmpMaster A LEFT JOIN Employee B on
A.ID=B.EmployeeID

In this left join query EmpMaster table will fetch all records and Employee table will fetch matched Id records with EmpMaster table and, if ID is not match it will return null values for Employee table

Q. What is a self-join?

Ans: Self join is used to join the same table with instance. I can be used to compare the rows within same table. to join same table you need to create an instance of the table.

Q. Can we join Multiple Tables in SQL Query

Ans : Yes, you can join more than two tables by with multiple JOIN clauses or you can use subqueries to join multiple tables.

  • Example Join Multiple Tables *

select
A.Name,
A.Email,
B.EmployeeSalary,
B.EmployeeDOB
from EmpMaster A ,
Employee B LEFT JOIN Employee_1 C
on B.EmployeeID=C.EmployeeID
where
A.ID=B.EmployeeID

Q. What is the difference between a JOIN and a UNION?

Ans: A JOIN combines rows from two or more tables based on a related column, while a UNION combines the results of two or more SELECT queries into a single result set.

Join is used to combine roes from different tables, UNION is used to combine the result of two different tables or SQL queries.

Q. Are SQL joins only two tables?

Ans: No, SQL joins can be joining multiple tables. SQL join can combine more than two tables.