5 Powerful SQL Joins You Must Master for Efficient Database Queries

SQL joins

• SQL joins are a pertinent concept in SQL, where they are simply a technique used to join data from the tables.
• The basic technical aspects of SQL joins include the retrieval of data from more than one table through a single query, and this is what makes them useful when you want to work with normalized databases.
• A database is created to figure out the connection between different sectors such as sales and customers, or products and categories following the SQL terminology of joins.
• Inner Join, Left Join, Right Join, Full Outer Join, and Cross Join are referred to as one of the several SQL joins.
• We will now examine in detail each kind, providing clear examples and explanations.

1. INNER JOIN

• An INNER JOIN is a type of database join that retrieves rows from both tables where there is a match on the specified columns.
• Depending on the match it finds, the INNER JOIN includes or discards the rows from the result.
• The INNER JOIN is the most popular and utilized version of JOIN of all the types, not only in SQL but in any database.

Example: Consider two tables:

• employees table:

• departments table:

• SQL Query:

SELECT employees.name, departments.department_name

FROM employees

INNER JOIN departments

ON employees.department_id = departments.department_id;

• Result:

• The query retrieves rows in which the department_id is present in both the employees and departments tables.

2. LEFT JOIN (or LEFT OUTER JOIN)

• A LEFT JOIN outputs all rows from the left table that contain rows with a key in the right table. In such a case, the NULL values are entered in the columns of the right table.
• Example: Take the same employees and departments tables as above, let’s say there is an employee who has a department but that particular department doesn’t exist (is not in the list) in that departments table.
• SQL Query:

SELECT employees.name, departments.department_name

FROM employees

LEFT JOIN departments

ON employees.department_id = departments.department_id;

• Result:

• The query brings in all the employees, the last one is the keywords David, that do not have a department that matches those that are in the departments table, thereby creating a NULL value for department_name.

3. RIGHT JOIN (or RIGHT OUTER JOIN)

• Another parallel practice between the LEFT JOIN and the RIGHT JOIN is that in the RIGHT JOIN besides the whole set of rows of the second table which is the table on the right being chosen, only those that meet the specified condition from the first table which is the table on the left will be chosen.
• If a record does not exist that matches, NULL values are generated for the corresponding columns of the left table.
• For instance, assume that there are more departments without any employees in the system.
• SQL Query:

SELECT employees.name, departments.department_name

FROM employees

RIGHT JOIN departments

ON employees.department_id = departments.department_id;

• Result: 

• This time and in this query, all the departments are displayed including the “Sales” one, which has no employees related to it. The name comes out NULL because the department has no employee attached to it.

4. FULL OUTER JOIN

• When you use A FULL OUTER JOIN, you merge the output of the LEFT JOIN and the RIGHT JOIN if there is information (data) from the two tables.
• If there is the same data present in the left or right table, all the rows will be returned, if at least one of the compared column’s values in the tables is not equal NULL will be returned for those rows with unmatched values in a table.
• For instance, let us take the example of the employees and departments tables that have unmatched employees (David, who doesn’t have a department) and unmatched departments (Sales, with no employees) involved in a situation.
• SQL Query:

SELECT employees.name, departments.department_name

FROM employees

FULL OUTER JOIN departments

ON employees.department_id = departments.department_id;

• Result:

• All the rows from both tables are retrieved. If there is no match, NULL is displayed for the non-matching table columns.

5. CROSS JOIN

• A cross join generates the cart product of the two tables.
• It gives all logically possible combinations of rows from both the tables.
• It can be time-consuming if the tables have large amounts of data since the result set will be huge.

Example: Suppose two small tables:

• colors table:

• sizes table:

• SQL Query:

SELECT colors.color, sizes.size

FROM colors

CROSS JOIN sizes;

• Result:

• The query provides all the possible combinations of colors and sizes, which makes the table have four different rows after it finishes.