JOINs are one of the most powerful features of SQL. They allow you to combine data from multiple tables into a single result set.
In this article, we will explore the basics of using JOINs in SQL.
- We will start by discussing the different types of JOINs that are available in SQL. Then we will look at some examples of how to use JOINs in SQL.
- Finally, we will discuss some best practices for using JOINs in SQL.
Types of JOINs:
There are four types of JOINs in SQL: INNER JOIN, OUTER JOIN, CROSS JOIN, and FULL OUTER JOIN.
Inner Join:
An inner join is the most common type of join. It combines data from two tables based on a common column or columns. The data from the two tables is combined into a single result set where each row in the result set has data from both tables.
Outer Join:
An outer join expands the result set of an inner join by including rows from one or both of the tables that were not included in the inner join. There are two types of outer joins: left outer join and right outer join.
Left Outer Join:
A left outer join includes all of the rows from the left table in the result set, even if there is no corresponding data in the right table. It also includes any rows from the right table that have no matching data in the left table.
Right Outer Join:
A right outer join is similar to a left outer join, but it includes all of the rows from the right table in the result set, even if there is no corresponding data in the left table. It also includes any rows from the left table that have no matching data in the right table.
Cross Join:
A cross joins combines data from two tables by matching every row in the first table with every row in the second table. The resulting result set is a matrix where the number of rows is equal to the product of the number of rows in the first and second tables.
Full Outer Join:
A full outer join expands the result set of an outer join by including all of the rows from both tables, even if there is no corresponding data in either table.
Example:
Let’s take a look at an example of how to use JOINs in SQL. We will start by creating two tables named “Employees” and “Salaries”. The Employees table will contain information about employees, including their name, salary, and department. The Salaries table will contain information about the salaries of employees, including their salary and department. To know more check RemoteDBA.com.
Here is the code for the Employees table:
CREATE TABLE Employees (Employee Name VARCHAR (50), Salary INT, Department VARCHAR (50))
Here is the code for the Salaries table:
CREATE TABLE Salaries (Salary INT, Department VARCHAR (50),)
Now let’s insert some data into these tables. First, we will insert data into the Employees table. Here is the SQL code for doing that:
INSERT INTO Employees (Employee Name, Salary, Department) VALUES (‘John’, 2000, ‘Accounting’), (‘Mary’, 2500, ‘Human Resources’), (‘Joe’, 3000, ‘IT’), (‘Jane’, 4000, ‘Accounting’)
Next, we will insert data into the Salaries table. Here is the SQL code for doing that:
INSERT INTO Salaries (Salary, Department) VALUES (2000, ‘Accounting’), (2500, ‘Human Resources’), (3000, ‘IT’), (4000, ‘Accounting’)
Now let’s take a look at the result set of a SELECT statement that uses an inner join. The SELECT statement will return the name and salary of every employee who is in the Accounting department.
Here is the SQL code for the SELECT statement:
SELECT Employee Name, Salary FROM Employees INNER JOIN Salaries ON Employees. Department = Salaries. Department WHERE Department=’Accounting’
The result set of this SELECT statement will be:
John 2000 Accounting
Mary 2500 Human Resources
Joe 3000 IT
Jane 4000 Accounting
Notice that the SELECT statement returned data for every employee who is in the Accounting department, even if they do not have a corresponding entry in the Salaries table. This is because the Employees table was joined with the Salaries table on the Department column, which means that the SQL engine looked for a match between each row in the Employees table and each row in the Salaries table. If there was no match, then that row was not included in the result set.
Conclusion:
In this article, we discussed the different types of JOINs that can be used in SQL. We looked at the inner join, which is the most common type of join, and we looked at how it works. We also looked at the other types of joins, including the left outer join, the right outer join, the cross joins, and the full outer join. Finally, we looked at an example of how to use a JOIN in a SELECT statement.