SQL Joins

Level : Intermediate

Mentor: Shailendra Chauhan

Duration : 00:02:00

SQL INNER JOIN

SQL INNER JOIN combines rows from two or more tables based on a specified condition and retrieves only the rows where the condition is met in all the joined tables. It returns a result set containing only the matching rows.

Example

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

SQL LEFT JOIN

SQL LEFT JOIN retrieves all rows from the left table and the matching rows from the right table based on a specified condition. If there are no matches in the right table, it returns NULL values for the right table's columns.

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

SQL RIGHT JOIN

SQL RIGHT JOIN is similar to the LEFT JOIN but retrieves all rows from the right table and the matching rows from the left table based on a specified condition. If there are no matches in the left table, it returns NULL values for the left table's columns.

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

SQL FULL OUTER JOIN

SQL FULL OUTER JOIN combines rows from two or more tables based on a specified condition and retrieves all rows from both tables, including rows where the condition is not met in one or both tables. It returns NULL values for columns where there is no match.

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Cross Join

SQL CROSS JOIN returns the Cartesian product of two or more tables, meaning it combines all rows from each table with all rows from the other tables, resulting in a potentially large result set.

Example

SELECT Customers.CustomerName, Products.ProductName
FROM Customers
CROSS JOIN Products;

Self Join

SQL Self Join is used to join a table to itself. It is often used when a table contains hierarchical data or when you need to compare rows within the same table.

Example

SELECT E1.EmployeeName, E2.EmployeeName AS Manager
FROM Employees E1
LEFT JOIN Employees E2 ON E1.ManagerID = E2.EmployeeID;

Self-paced Membership

Upto 60% OFF

Know More

Still have some questions? Let's discuss.