Understanding 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 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:

1. What are Joins?

2. How many types of Joins are there in SQL?

3. How do I know which join to use in SQL

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

4. Most Common Questions asked about 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 that 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.

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.

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.

SELECT Table1.Column1,Table1.Column2,Table2.Column1,....
FROM Table1
INNER JOIN Table2
ON Table1.MatchingColumnName = Table2.MatchingColumnName;
SELECT Employee.EmpID, Employee.EmpFname, Employee.EmpLname, Projects.ProjectID, Projects.ProjectName FROM Employee INNER JOIN Projects ON Employee.EmpID=Projects.EmpID;

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;

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 ;

Output:

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;

Output:

Now, let us move forward with our next section in this article i.e. the top questions asked about SQL Joins in your interviews.

Most Common Questions asked about Joins

Question 1: What is a Natural Join and in which situations is a natural join used?

A Natural Join is also a Join operation that is used to give you an output based on the columns in both the tables between which, this join operation must be implemented. To understand the situations n which natural join is used, you need to understand the difference between Natural Join and Inner Join.

The main difference the Natural Join and the Inner Join relies on the number of columns returned. Refer below for example.

Now, if you apply INNER JOIN on these 2 tables, you will see an output as below:

If you apply NATURAL JOIN, on the above two tables, the output will be as below:

Question 2: How to map many-to-many relationships using joins?

To map many to many relationships using joins, you need to use two JOIN statements.

For example, if we have three tables(Employees, Projects and Technologies), and let us assume that each employee is working on a single project. So, one project cannot be assigned to more than one employee. So, this is basically, a one-to-many relationship.

Now, similarly, if you consider that, a project can be based on multiple technologies, and any technology can be used in multiple projects, then this kind of relationship is a many-to-many relationship.

To use joins for such relationships, you need to structure your database with 2 foreign keys. So, to do that, you have to create the following 3 tables:

• Projects
• Technologies
• projects_to_technologies

The project_to_technologies table holds the combinations of project-technology in every row. This table maps the items on the projects table to the items on the technologies table so that multiple projects can be assigned to one or more technologies.

Once the tables are created, use the following two JOIN statements to link all the above tables together:

• projects_to_technologies to projects
• proejcts_to-technologies to technologies

Question 3: What is a Hash Join?

Hash joins are also a type of joins which are used to join large tables or in an instance where the user wants most of the joined table rows.

The Hash Join algorithm is a two-step algorithm. Refer below for the steps:

• Build phase: Create an in-memory hash index on the left side input
• Probe phase: Go through the right side input, each row at a time to find the matches using the index created in the above step.

Question 4: What is Self & Cross Join?

Self Join

SELF JOIN in other words is a join of a table to itself. This implies that each row in a table is joined with itself.

Cross Join

The CROSS JOIN is a type of join in which a join clause is applied to each row of a table to every row of the other table. Also, when the WHERE condition is used, this type of JOIN behaves as an INNER JOIN, and when the WHERE condition is not present, it behaves like a CARTESIAN product.

Question 5: Can you JOIN 3 tables in SQL?

Yes. To perform a JOIN operation on 3 tables, you need to use 2 JOIN statements. You can refer to the second question for an understanding of how to join 3 tables with an example.

Now with this, we come to an end of this comparison on SQL vs NoSQL. I hope you guys enjoyed this article and understood all the differences. If you wish to check out more articles on the market’s most trending technologies like Artificial Intelligence, DevOps, Ethical Hacking, then you can refer to Edureka’s official site.

Do look out for other articles in this series which will explain the various other aspects of SQL.

1. Differences Between SQL & NoSQL Databases

2. SQL For Data Science

3.Top 65 SQL Interview Questions

Originally published at https://www.edureka.co on April 2, 2019.