JOINs

✅ DO use INNER JOIN for merging two or more tables rather than using the WHERE clause.#

Consider this query below:

SET 
  STATISTICS IO ON 
  
SELECT 
  p.Name, 
  Color, 
  ListPrice 
FROM 
  SalesLT.Product p, 
  SalesLT.ProductCategory pc 
WHERE 
  P.ProductCategoryID = pc.ProductCategoryID

This type of query is a cartesian join. In this type of join, all possible combinations are created which means it will retrieve all records first then filter it from there. This will cause the database to slow down especially for large-scale databases. To prevent this INNER JOIN can be used.

SET 
  STATISTICS TIME ON 
SELECT 
  p.Name, 
  Color, 
  ListPrice 
FROM 
  SalesLT.Product p 
  INNER JOIN SalesLT.ProductCategory pc ON P.ProductCategoryID = pc.ProductCategoryID

In here, the database will only generate the desired number of records that has the same ProductCategoryID. Some database management system automatically recognizes the WHERE joins and run them like INNER JOIN but some does not so it is recommended to use JOIN instead.

So, we can see from the above outputs that inner join takes almost half time as compared to join using WHERE clause.

Learn More#

A Detailed Guide on SQL Query Optimization

SQL Query Optimization