What is Joins in SQL - Explore Its Different Types with Example

Table of Contents [show]

In our everyday computer systems, we organize information into different groups called tables, and these tables are connected through shared values, like keys, in databases such as SQL Server, Oracle, MySQL, and others. However, it is how we make sense of and manage our data. Now, imagine you want to gather information from two or more of these tables and display it in a way that makes sense. Well, that's where the SQL JOIN clause comes in handy. This article is here to help you understand joins in SQL and its different types, with a simple example.

Meaning of Joins in SQL

The join clause is a helpful tool that lets us bring together information from different tables so that it makes sense to us. We use it with a SELECT statement and some instructions on connecting these tables. This instruction tells SQL Server how to use information from one table to find and show us the correct rows from another table. Besides that, these connect through foreign key constraints.

Pick the parts of the tables that you want to put together. To accomplish this, you'll need to choose which columns from each table you want to use in this joining process. Now, a join condition tells the computer which piece from one table matches with a piece from another. Select the logical operator to compare values between columns, such as =, <, or >.

Different Types of Joins in SQL

In SQL Server, there are various types of joins. Each type of join is like a way to connect two tables in a unique way when you're asking the computer for information. Here are the types of joins that SQL Server supports:

1. INNER JOIN

The INNER JOIN keyword brings together rows from two tables, but only if they have something in common. It selects all the rows where the condition connects – where the values in a particular shared field are the same.

How to Use Inner Join in SQL?
 

Suppose we're getting information from more than one table. To make things simple, we give each table a short name called an alias, especially when they have similar names for their stuff, like the 'id' field.

Now, when we're picking what we want to see (like employee names and department names), we use these short names to tell the computer which table each piece of information comes from.

To make sure we're only getting the right matches, we use a condition in the WHERE part of our request. In other words, you say, 'Only give me the info where the department ID in the employees' table matches the ID in the departments' table.' It's a way of connecting the dots.

So, for each employee, the computer looks at their department ID, finds the matching department with the same ID, and brings back the name. As a result, it happens for every employee on the list.

2. LEFT JOIN

You have two sets of information and want to see everything from the first set, along with whatever matches from the second set. It is known as LEFT JOIN. In short, it creates a list of all the things from the first set and if something from the second set matches. But if there's no match, we still include the first set's info, leaving a blank space for the second set. It's also called LEFT OUTER JOIN because it keeps all the stuff on the left and brings in what matches from the right.

How to Use Left Outer Joins in SQL?
 

For example, you want a list of all employees and their departments, but you don't want to miss anyone. As a result, this is where you'd use a LEFT JOIN. With a LEFT JOIN, you get every employee's name from the main list (the left table), and then you add the department names where they match. In other words, you are saying, 'Give me everyone, and if they have a department, tell me that too.'

They also call it LEFT OUTER JOIN, but you can use the terms interchangeably. You change your query and use a LEFT JOIN to get all the employee and department names.

3. RIGHT JOIN:

RIGHT JOIN creates a list where you want everything from the second set and whatever matches from the first set. So, you get everything from the right side and add whatever matches from the left side. If nothing matches on the left, no problem – you still get all the info from the right, just with a space where there's no match. In addition, they also call it RIGHT OUTER JOIN.

How to Use Right Joins in SQL?
 

RIGHT JOIN is the opposite of LEFT JOIN. With RIGHT JOIN, you start by getting all the stuff from the table on the right side, the one you're joining. Then, if there's anything from the left table, your main list, that matches, you add that in.

They also call it RIGHT OUTER JOIN – you can use these terms interchangeably. So, imagine you have a list, and you want everything from the second set, and if there's anything from the first set that fits, you include that. To do this, you Ymodify your query to use RIGHT JOIN instead of LEFT JOIN.

4. FULL (OUTER) JOIN

FULL JOIN IS a combination of LEFT JOIN and RIGHT JOIN. So, with FULL JOIN, you're making a bulky list that includes everything from both sets, the left and the right. But if there's no match, don't worry! You still get all the rows from both tables. You just need to go with empty spaces where there's no match.

How to Use FULL Outer Join in SQL?
 

For example, we have a pretend supermarket with two lists of information. The first list is all about customers, like their unique ID, name, and phone number. Each customer has a unique ID, and we call it CustID.

Now, the second list is about shopping details. It tells us what items were bought by customers, like the item's unique ID, the customer's ID (showing who bought it), the item's name, and how many of those items they purchased.

5. CROSS JOIN

Cross Join in SQL Server mixes and matches all the options from two or more tables to create a bulky list. We call it CROSS JOIN or even CARTESIAN JOIN. As a result, it makes a combination of all the rows from one table with all the rows from another. It's making every possible pair you can think of. In other words, all the rows from the first table get paired with all the rows from the second table.

How to Use Cross Joins in SQL?
 

Suppose you have two lists: one with six items (let's call it the employees list) and another with four items (the departments list). Now, if you want to pair up every item from the first list with every item from the second list, you'll get a total of 24 pairs. So, in this example, you end up with 24 rows, which is just the result of multiplying the number of items in the employee's list by the number of items in the department's list.

6. SELF JOIN

SELF JOIN is a table having a conversation with itself. For example, you have a list of things and want to connect each item with every other item in the same list. To make this work, we use table name aliases, like the table's nicknames. However, this helps us keep things organized when we're talking to the table in our queries.

So, with a SELF JOIN, you're comparing rows inside the same table. Moreover, it is handy to dig into hierarchical data or check out how different rows in the table relate to each other.

How to Use SELF Joins in SQL?
 

Suppose you have this unique table with details about employees. In some cases, employees can also be managers, and the table has info like their ID, name, and who their manager is.

Let's say you want to ask the computer for a list of managers and count how many employees each of them has. Since managers are also at the same table as regular employees, you only need to talk to that one table, the employee's table, to get all the info you need.

Our Learners Also Reads: What is Structured Query Language (SQL) - SQL Tutorial Guide
 

SQL Join Example

Now, the following section will discuss an example of SQL Join.

-- Join the Customers and Orders tables

-- based on the common values of their customer_id columns

SELECT Customers.customer_id, Customers.first_name, Orders. item

FROM Customers

JOIN Orders

ON Customers.customer_id = Orders.customer_id;

Conclusion

Joins in SQL help to put together and get proper information from databases. Using different join types allows for the database to communicate more effectively. However, it makes it easy to get the exact info you're looking for and make your database queries work. In short, we have provided you with an introduction, types, and examples of joins. Read and understand the above guide concept.