Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
What are SQLJoins?
3.
Types of Joins
3.1.
Inner Join
3.2.
Left Join (or Left Outer Join)
3.3.
Right Join (or Right Outer Join)
3.4.
Full Join (or Full Outer Join)
3.5.
Cross Join
4.
What is Subquery?
5.
Types of Subqueries
5.1.
Scalar Subquery
5.2.
Correlated Subquery
5.3.
Nested Subquery
6.
SQL Join vs Subquery
7.
Advantages
8.
Limitations
9.
Examples of Joins and Subqueries
9.1.
Simple Join
9.2.
Inner Join
9.3.
Left Join
9.4.
Correlated Subquery
9.5.
Nested Subquery
9.6.
Cross Join
10.
When to Use Joins vs. Subqueries
10.1.
Use Joins when:
10.2.
Use Subqueries when:
11.
Frequently Asked Questions
11.1.
Why use Subquery instead of Join?
11.2.
Which join is faster in SQL?
11.3.
Is a subquery more efficient than a join?
12.
Conclusion
Last Updated: Mar 27, 2024
Medium

Difference between join and subquery

Author Sinki Kumari
0 upvote
gp-icon
Dbms - Database management systems
Free guided path
12 chapters
93+ problems
gp-badge
Earn badges and level up

Introduction

SQL (Structured Query Language) is a powerful tool for managing and manipulating data in relational databases. When it comes to retrieving data from multiple tables or applying conditional filtering, two important concepts come into play: Joins and Subqueries.  
Joins help in combining rows from multiple tables based on a related column. while subqueries help in filtering rows and returning values of the outer query. Joins and Subqueries can be used to achieve the same goal depending on the particular requirement of the query. They both play a crucial role in extracting relevant information from complex datasets, but they have distinct functionalities and use cases.

Difference between Join and subquery

In this comprehensive article, we will delve deep into the differences between Joins and Subqueries in SQL, exploring their syntax, examples, advantages, and limitations.

What are SQLJoins?

SQL Join is a mechanism used to combine data from two or more tables based on a related column or set of columns. It enables us to retrieve information that is distributed across different tables in a single result set. Joins are especially useful when dealing with normalized databases, where data is stored in separate tables to reduce redundancy and maintain data integrity.

The syntax for a Join typically involves specifying the tables to be joined, the Join type, and the condition on which the tables are related. The most common Join types are INNER JOIN, LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (or RIGHT OUTER JOIN)and FULL JOIN (or FULL OUTER JOIN).

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

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?

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

TABLE

Departments

TABLE

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:

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:

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:

OUTPUT

Correlated Subquery

Consider two tables: Orders and Customers.

Orders

TABLE

Customers

TABLE

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:

corelated subquery

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:

OUTPUT

Cross Join

Consider two tables: Products and Suppliers.

Products

TABLE

Suppliers

TABLE

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

SELECT ProductName, SupplierName
FROM Products
CROSS JOIN Suppliers;


Output:

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.

You may refer to our Guided Path on Code Ninjas Studios for enhancing your skill set on DSACompetitive ProgrammingSystem 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!

Previous article
Composite Key in SQL
Next article
PIVOT and UNPIVOT in SQL
Guided path
Free
gridgp-icon
Dbms - Database management systems
12 chapters
93+ Problems
gp-badge
Earn badges and level up
Live masterclass