YogeshChauhan . com

A Quick Comparison of JOIN and Subquery in SQL

in SQL/MySQL on January 6, 2020

A subquery is simply query inside a query. Nested. While JOIN is like a merger of two tables to form a new table virtually. It combines data from two different tables or the table itself(self-join).

We can get many different results from using Subquery and JOIN which, many times, can't be done by simple SQL query. But most of the subqueries can be rewritten using JOIN and vice versa. Let's take a look at the examples.

Let's take a look at the examples to understand this better.


//JOIN example

SELECT invoice_number, invoice_date, invoice_total FROM invoices JOIN sellers
ON invoices.seller_id = sellers.seller_id
WHERE seller_state='FL'
ORDER BY invoice_date;

The query above is fetching the number, date and total for each invoices and joins with sellers on sellers_id. Also, we have a condition in WHERE which will limit the result set to Florida sellers only.

Now take a look the following query.


//Subquery example

SELECT invoice_number, invoice_date, invoice_total FROM invoices
WHERE seller_id IN
(SELECT seller_id FROM sellers WHERE seller_state= 'FL')
ORDER BY invoice_date;

In the first query I have used JOIN to combine the sellers and invoices data and then I am checking for the state using WHERE condition.

In the second query, I have added a subquery in which it will only return the seller_id of Florida sellers. So, in that query I am fetching the data for only Florida sellers from invoices using WHERE condition.

So, what should we use?

I think we should use the query based on the complexity and relationship between the tables as well. But that's my opinion.

JOIN is more helpful when there is a relationship between tables like the query above. They both have seller_id column in common. Subquery is helpful whether there is a relation or not (ad hoc relationship we can say).

For complex queries subqueries are easier to use and divide the query in small parts and analyze from human eyes (more readable, more understandable). We don't always find relationships between all tables in complex queries so using subqueries might be a good idea. 

Advantages of JOIN

  • It included columns in result set from both tables.
  • More helpful/intuitive when there is a relationship between tables.

Advantages of Subquery

  • We can pass aggregate value using subquery
  • More helpful/intuitive when there is no relationship or more like ad hoc relationship
  • It makes complex and lengthy queries easier to write, more readable and understandable
amazon

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 How to Use SQL MAX() Function with Dates? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

Jun 16 What are Stored Procedures for SQL Server? Jun 16 What are Class Constants in PHP? Jun 15 A short basic guide on states in React Jun 15 How to define constants in PHP? Jun 15 How to define visibility for a property in PHP? Jun 15 How to use @if and @else in SCSS?

You might also like these

Five common features of Angular template syntax (with examples)AngularHow to create a Progress Bar using JavaScript?JavaScriptLearn how to add Scroll Indicator using CSS and JavaScript?CSSasync function in JavaScriptJavaScriptHTML Copyright SymbolHTMLHow to add recaptcha version 3 to PHP website?PHP