What is a SQL Joins ?

SQL JOINS are used to combine records from two or more tables in a database based on a related column between them. Joins allow you to retrieve meaningful information by linking data from different tables.

What are the different types of SQL Joins ?

The different types of SQL JOINs are:

INNER JOIN
LEFT JOIN (LEFT OUTER JOIN)
RIGHT JOIN (RIGHT OUTER JOIN)
FULL JOIN (FULL OUTER JOIN)
CROSS JOIN
SELF JOIN

INNER JOIN

An Inner Join returns only the rows where there is a match between both tables based on a specified condition (like matching IDs or values). If there is no match for a row in one of the tables, that row is excluded from the results. This type of join is used when you only want the data that exists in both tables.

Example use case: You have a table of customers and a table of orders. If you want to find customers who have placed orders, you would use an Inner Join to only return customers who have matching orders.

Left Jion(Left Outer Join)

A Left Join returns all the rows from the left table (the first table) and the matching rows from the right table (the second table). If there is no match for a row in the left table, the result will still include that row, but with NULL values for the columns of the right table. This join ensures that no data from the left table is lost, even if there’s no matching data in the right table.

Example use case: You have a table of customers and a table of orders. If you want to list all customers and their orders (even customers who haven’t placed any orders), you would use a Left Join. The customers without orders will have NULL values in the order-related columns.

Right Join (Right Outer Join)

A Right Join is the opposite of a Left Join. It returns all the rows from the right table (the second table) and the matching rows from the left table (the first table). If there is no match for a row in the right table, the result will still include that row, but with NULL values for the columns from the left table.

Example use case: You have a table of orders and a table of customers. If you want to list all orders and the customers who placed them (including orders that may not be associated with a customer), you would use a Right Join. Orders without customers will have NULL values in the customer-related columns.

Full Join (Full Outer Join)

A Full Join returns all the rows from both tables, whether or not there is a match between them. If a row in one table doesn’t have a matching row in the other table, the result will include that row with NULL values for the missing side. This join is useful when you want to make sure no data is excluded from either table.

Example use case: You have a table of employees and a table of projects. If you want to list all employees and all projects, including those employees who are not assigned to a project and projects that don’t have any employees assigned, you would use a Full Join.

Cross Join

A Cross Join returns the Cartesian product of both tables, meaning it combines every row from the first table with every row from the second table. There is no condition or matching requirement in a Cross Join. This can result in a very large number of rows if both tables have a lot of data, as each row from the first table is combined with every row from the second table.

Example use case: You have a table of products and a table of colors. If you want to list every combination of product and color, regardless of whether they are related, you would use a Cross Join.

Self Join

A Self Join is when a table is joined with itself. This is useful when a table contains hierarchical data or when you need to compare rows within the same table. For example, if you have an employee table where each employee has a manager (who is also an employee), you can use a Self Join to match employees with their managers.

Example use case: You have an employee table with an employee_id and a manager_id. If you want to find each employee along with their manager, you would perform a Self Join, comparing the manager_id with the employee_id in the same table.

Latest Posts

All Posts
Software Testing
Uncategorized

End of Content.

What is a SQL Joins ?