Understanding about SQL Joins, All You Need To Know About SQL Joins

In this article Understanding about SQL Joins, All You Need To Know About SQL Joins

Structured Query Language aka 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 which are just some of the most important concepts to master in SQL.

The following topics will be covered in this article

  • What are Joins?
  • How many types of Joins are there in SQL?
  • How do I know which join to use in SQL
    • INNER JOIN
    • FULL JOIN
    • LEFT JOIN
    • RIGHT JOIN
  • Most Common Questions asked about Joins

Understanding about SQL Joins, All You Need To Know About SQL Joins

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.

How do I know which join to use in SQL?

JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Let’s look at a selection from the “Orders” table:

OrderIDCustomerIDOrderDate
1030821996-09-18
10309371996-09-19
10310771996-09-20

Then, look at a selection from the “Customers” table:

CustomerIDCustomerNameContactNameCountry
1Alfreds FutterkisteMaria AndersGermany
2Ana Trujillo Emparedados y heladosAna TrujilloMexico
3Antonio Moreno TaqueríaAntonio MorenoMexico

Notice that the “CustomerID” column in the “Orders” table refers to the “CustomerID” in the “Customers” table. The relationship between the two tables above is the “CustomerID” column.

Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables:

SQL Statement:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

and it will produce something like this:

OrderIDCustomerNameOrderDate
10308Ana Trujillo Emparedados y helados9/18/1996
10365Antonio Moreno Taquería11/27/1996
10383Around the Horn12/16/1996
10355Around the Horn11/15/1996
10278Berglunds snabbköp8/12/1996
joins explanation 1

How many types of Joins are there in SQL?

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

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;
inner join

Example

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

Output:

EmpIDEmpFnameEmpLnameProjectIDProjectName
1VardhanKumar111Project1
2HimaniSharma222Project2
3AayushiShreshth333Project3
3AayushiShreshth444Project4
5SwateeKapoor555Project5

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;
full join

Example

SELECT Employee.EmpFname, Employee.EmpLname, Projects.ProjectID
FROM Employee
FULL JOIN Projects
ON Employee.EmpID = Projects.EmpID;
EmpFnameEmpLnameProjectID
VardhanKumar111
 HimaniSharma222
AayushiShreshth333
AayushiShreshth444

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;
left joion 1

Example:

SELECT Employee.EmpFname, Employee.EmpLname, Projects.ProjectID, Projects.ProjectName
FROM Employee
LEFT JOIN
ON Employee.EmpID = Projects.EmpID ;
EmpFnameEmpLnameProjectIDProjectName
VardhanKumar111Project1
HimaniSharma222Project2
AayushiShreshth333Project3
AayushiShreshth444Project4
SwateeKapoor555Project5
HemanthSharmaNULLNULL

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;
right join 1

Example:

SELECT Employee.EmpFname, Employee.EmpLname, Projects.ProjectID, Projects.ProjectName
FROM Employee
RIGHT JOIN
ON Employee.EmpID = Projects.EmpID;
EmpFnameEmpLnameProjectIDProjectName
VardhanKumar111Project1
HimaniSharma222Project2
AayushiShreshth333Project3
AayushiShreshth444Project4
SwateeKapoor555Project5
NULLNULL666Project6
NULLNULL777Project7
 NULL NULL888Project8

Also Read Difference between Primary Key and Foreign Key in MySQL Database

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

How to Protect yourself from CovidVaccination saves lives at every stage of life
  • Clean your home regularly, particularly frequently touched surfaces like
  • Stay home stay safe. Follow the Golden Rule. Wash your hands frequently with soap and water or use alchohol based hand-rub.
  • Stay home stay safe. Stay physically fit. Exercise regulary. Eat a nutritious diet. Don’t smoke.
  • Stay home stay safe. If you show symptoms of COVID-19, self isolate yourself, wear a mask around others and seek medical advice