Difference between join and subquery

Types of Joins

Inner Join

The Inner Join retrieves only the rows from both tables that have matching values based on the specified condition. It excludes the rows where there is no match between the tables. The syntax for an Inner Join is as follows:

SELECT col1, col2, ...
FROM tab1
INNER JOIN tab2
ON tab1.column = tab2.column;

Left Join (or Left Outer Join)

The Left Join returns all the rows from the left table and the matching rows from the right table. If there is no match in the right table, the result will contain NULL values for the right table columns. The syntax for a Left Join is as follows:

SELECT col1, col2, ...
FROM tab1
LEFT JOIN tab2
ON tab1.column = tab2.column;

Right Join (or Right Outer Join)

The Right Join is similar to the Left Join but returns all the rows from the right table and the matching rows from the left table. If there is no match in the left table, the result will contain NULL values for the left table columns. The syntax for a Right Join is as follows:

SELECT col1, col2, ...
FROM tab1
RIGHT JOIN tab2
ON tab1.column = tab2.column;

Full Join (or Full Outer Join)

The Full Join combines the results of both the Left Join and Right Join. It returns all the rows from both tables and includes NULL values where there is no match in either table. The syntax for a Full Join is as follows:

SELECT col1, col2, ...
FROM tab1
FULL JOIN tab2
ON tab1.column = tab2.column;

Cross Join

The Cross Join, also known as Cartesian Join, combines all rows from the first table with all rows from the second table. It results in the Cartesian product of both tables. The syntax for a cross-join is as follows:

SELECT col1, col2, ...
FROM tab1
CROSS JOIN tab2;

What is Subquery?

A Subquery, also known as an Inner Query or Nested Query, is a query that is embedded within another SQL query. Subqueries are used to retrieve data based on the results of another query. They act as a single value or a temporary table that filters or modifies the main query's results. Subqueries can be used with various SQL commands like SELECT, INSERT, UPDATE, and DELETE.

The syntax for using Subqueries depends on the SQL command and the position of the Subquery within it. Subqueries are typically used in the WHERE clause, HAVING clause, or in the column list of a SELECT statement.

Also see, SQL EXCEPT

Types of Subqueries

Scalar Subquery

A Scalar Subquery returns a single value, such as a single column or an aggregate function result. It is used as an expression within the main query. The main query can compare, filter, or perform computations based on the result of the Scalar Subquery.

SELECT col1, col2, ...
FROM tab
WHERE col1 = (SELECT colX FROM secondtab WHERE condition);

Correlated Subquery

A Correlated Subquery is a Subquery that depends on the values from the outer query. It means that the Subquery is executed for each row of the outer query. Correlated Subqueries use the values of the outer query to filter or find related data in the Subquery.

SELECT col1, col2, ...
FROM tab t1
WHERE col1 OPERATOR (SELECT colX FROM secondtab t2 WHERE t2.colY = t1.colZ);

Nested Subquery

A Nested Subquery is a Subquery that is placed inside another Subquery. It can be used to retrieve more complex data or to perform multiple levels of filtering based on different conditions.

SELECT col1, col2, ...
FROM tab
WHERE col1 IN (SELECT colX FROM secondtab WHERE condition);

SQL Join vs Subquery

SQL JOINS	SUBQUERIES
Use the appropriate Join type based on the relationship between the tables and the desired results.	Use Subqueries when necessary, but strive to keep them simple and straightforward.
Always specify the Join conditions explicitly and avoid using implicit Joins.	Avoid using Subqueries within loops or heavily nested queries, as they can impact performance.
Avoid using Cross Joins without a specific purpose, as they can lead to a large number of result rows.	Use Correlated Subqueries judiciously and consider alternatives like Joins or temporary tables for complex scenarios.
Ensure that the columns involved in the Join conditions are indexed for improved performance.	Ensure that the columns used in Subquery conditions are indexed to optimize performance.
Test and optimize Joins for performance, especially when dealing with large datasets.	Test and analyze the performance of Subqueries to identify potential bottlenecks.

Advantages

Let us learn the difference between join and subquery on the basis of their advantages.

JOINS	SUBQUERIES
Joins allow us to combine data from multiple tables, making it easier to access related information.	Subqueries enable us to perform complex filtering and calculations based on the results of another query.
Joins provide a clear and concise way to retrieve data without the need for additional filtering or processing.	Subqueries can be used to retrieve specific rows or values that would be difficult to achieve using Joins alone.
Joins are efficient when dealing with large datasets and indexed columns.	Subqueries are flexible and can be used with various SQL commands, providing greater versatility in querying data.

Limitations

Following is a table depicting how join and subquery differ in terms of their limitations.

JOINS	SUBQUERIES
Joins can be resource-intensive, especially when dealing with large tables or multiple complex Join conditions.	Subqueries can decrease query performance, especially when used with large datasets or in nested scenarios.
An incorrect Join condition may lead to unexpected results or a Cartesian product, which can severely impact performance.	If not optimized properly, correlated Subqueries can execute multiple times, leading to inefficiencies.
While Joins work well for combining data from different tables, they may not be suitable for certain complex data manipulations.	Subqueries can be challenging to maintain and may make the SQL code more complex and harder to read.

Examples of Joins and Subqueries

Simple Join

Consider two tables: Employees and Departments.

Employees

Departments

To retrieve the names of employees along with their department names, we can use an Inner Join.

SELECT Employees.Name, Departments.Department
FROM Employees
INNER JOIN Departments
ON Employees.DeptID = Departments.DeptID;

Output:

Inner Join

Using the same Employees and Departments tables, let's find the employees and their department names where the department name contains the letter "g."

SELECT Employees.Name, Departments.Department
FROM Employees
INNER JOIN Departments
ON Employees.DeptID = Departments.DeptID
WHERE Departments.Department LIKE '%g%';

Output:

Left Join

Let's retrieve all employees and their respective department names. If an employee does not belong to any department, their department name should be shown as "Not Assigned."

SELECT Employees.Name, COALESCE(Departments.Department, 'Not Assigned') AS Department
FROM Employees
LEFT JOIN Departments
ON Employees.DeptID = Departments.DeptID;

Output:

Correlated Subquery

Consider two tables: Orders and Customers.

Orders

Customers

To retrieve orders placed by customers along with their names, we can use a correlated Subquery.

SELECT OrderID, OrderDate, (SELECT CustomerName FROM Customers WHERE Customers.CustID = Orders.CustID) AS CustomerName
FROM Orders;

Output:

Nested Subquery

Let's find the customers who placed an order in the last 30 days.

SELECT CustomerName
FROM Customers
WHERE CustID IN (
    SELECT CustID
    FROM Orders
    WHERE OrderDate >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
);

Output:

Cross Join

Consider two tables: Products and Suppliers.

Products

Suppliers

To retrieve all possible combinations of products and suppliers, we can use a Cross Join.

SELECT ProductName, SupplierName
FROM Products
CROSS JOIN Suppliers;

Output:

Also see,  loop and while loop

When to Use Joins vs. Subqueries

Use Joins when:

• You need to combine data from multiple tables based on related columns
   
• The result set should include only matching rows from both tables
   
• You want to retrieve data in a single result set efficiently

Use Subqueries when:

• You need to filter data based on the results of another query
   
• The main query requires a computed value based on a Subquery's result
   
• The logic cannot be efficiently expressed using Joins alone

Frequently Asked Questions

Why use Subquery instead of Join?

In many cases, Join is more efficient, but there are some cases in which constructs other than a subquery are not possible. JOINs are easier to read for experienced SQL coders, while Subqueries are easier to read for beginners.

Which join is faster in SQL?

Usually, an OUTER JOIN is slower compared to an INNER JOIN as it needs to return more number of record than an INNER JOIN.

Is a subquery more efficient than a join?

Joins execute faster compared to subqueries. A join-based query retrieval time will nearly always be faster than one that uses a subquery.

Conclusion

The article discussed that Joins excel at combining data from multiple tables, while Subqueries enable us to filter and compute data based on the results of other queries. Alright! So now that we have learned the difference between join and subquery, you can refer to other similar articles.

• Sql functions
• Subqueries
• SQL joins Interview Questions
• Joins

You may refer to our Guided Path on Code Ninjas Studios for enhancing your skill set on DSA, Competitive Programming, System Design, etc. Check out essential interview questions, practice our available mock tests, look at the interview bundle for interview preparations, and so much more!

Happy Learning!