SQL Joins – All You Need To Know About SQL Joins

Structured Query Language i.e SQL is the core of relational databases with the help of which we can handle data. It provides us with various features such as Triggers, Injection, Hosting and, Joins is just one of the most important concept to master in SQL. In this article on SQL Joins, I will discuss the various types of Joins used in SQL.

The following topics will be covered in this article :

What are Joins?

JOINS in SQL are commands which are used to combine rows from two or more tables, based on a related column between those tables.  There are predominantly used when a user is trying to extract data from tables which have one-to-many or many-to-many relationships between them.

Now, that you know what joins mean, let us next learn the different types of joins.

How many types of Joins are there in SQL?

There are mainly four types of joins that you need to understand. They are:

  • INNER JOIN
  • FULL JOIN
  • LEFT JOIN
  • RIGHT JOIN

You can refer to the below image.

SQL Joins – All You Need To Know About SQL Joins

How do I know which join to use in SQL?

Let us look into each one of them. For your better understanding of this concept, I will be considering the following three tables to show you how to perform the Join operations on such tables.

SQL Joins – All You Need To Know About SQL Joins

Client Table

INNER JOIN

This type of join returns those records which have matching values in both tables. So, if you perform an INNER join operation between the Employee table and the Projects table, all the tuples which have matching values in both the tables will be given as output.

Syntax:

SELECT Table1.Column1,Table1.Column2,Table2.Column1,....
FROM Table1
INNER JOIN Table2
ON Table1.MatchingColumnName = Table2.MatchingColumnName;
NOTE: You can either use the keyword INNER JOIN or JOIN to perform this operation. 

Example:

 
 
 
SELECT Employee.EmpID, Employee.EmpFname,
Employee.EmpLname, Projects.ProjectID, Projects.ProjectName
FROM Employee
INNER JOIN Projects ON Employee.EmpID=Projects.EmpID;

FULL JOIN

Full Join or the Full Outer Join returns all those records which either have a match in the left(Table1) or the right(Table2) table.

syntax:

SELECT Table1.Column1,Table1.Column2,Table2.Column1,....
FROM Table1
FULL JOIN Table2
ON Table1.MatchingColumnName = Table2.MatchingColumnName;

Example:

 
 
 
 
SELECT Employee.EmpFname, Employee.EmpLname, Projects.ProjectID
FROM Employee
FULL JOIN Projects
ON Employee.EmpID = Projects.EmpID;
 

SQL Joins – All You Need To Know About SQL Joins

LEFT JOIN

The LEFT JOIN or the LEFT OUTER JOIN  returns all the records from the left table and also those records which satisfy a condition from the right table. Also, for the records having no matching values in the right table, the output or the result-set will contain the NULL values.

Syntax:

SELECT Table1.Column1,Table1.Column2,Table2.Column1,....
FROM Table1
LEFT JOIN Table2
ON Table1.MatchingColumnName = Table2.MatchingColumnName;

Example:

 
 
 
 
SELECT Employee.EmpFname, Employee.EmpLname,
Projects.ProjectID, Projects.ProjectName
FROM Employee
LEFT JOIN
ON Employee.EmpID = Projects.EmpID ;

SQL Joins – All You Need To Know About SQL Joins

 

 

 

 

RIGHT JOIN

The RIGHT JOIN or the RIGHT OUTER JOIN  returns all the records from the right table

and also those records which satisfy a condition from the left table.

Also, for the records having no matching values in the left table,

the output or the result-set will contain the NULL values.

Syntax:

SELECT Table1.Column1,Table1.Column2,Table2.Column1,....
FROM Table1
RIGHT JOIN Table2
ON Table1.MatchingColumnName = Table2.MatchingColumnName;

Example:

 
 
 
 
SELECT Employee.EmpFname, Employee.EmpLname,
Projects.ProjectID, Projects.ProjectName
FROM Employee
RIGHT JOIN
ON Employee.EmpID = Projects.EmpID;
SQL Joins – All You Need To Know About SQL Joins

0 Comments

Leave a Reply