SQL JOIN - WHERE clause vs ON clause

0 votes
What is the difference and what should go in each?
Sep 14, 2022 in Database by Kithuzzz
• 38,000 points
1,147 views

1 answer to this question.

0 votes

They are not the same thing.

Consider these queries:

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345

And

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID 
    AND Orders.ID = 12345

For order number 12345, the first will return an order together with any lines that it contains. All orders will be returned by the second, but only order 12345 will have any lines attached to it.

The clauses are effectively equal when used with an INNER JOIN. The two types of sentences do not, however, have the same semantic meaning only because they are functionally equivalent in that they provide the same outcomes.

I hope this helps you. 

answered Sep 16, 2022 by narikkadan
• 63,600 points

Related Questions In Database

0 votes
1 answer

IN vs OR in the SQL WHERE Clause

I assume you want to know the ...READ MORE

answered Sep 24, 2018 in Database by DataKing99
• 8,250 points
3,956 views
0 votes
1 answer

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

At the top level there are mainly ...READ MORE

answered Feb 4, 2022 in Database by Neha
• 9,020 points
1,999 views
0 votes
1 answer

SQL Switch/Case in 'where' clause

Without a case statement: SELECT column1, ...READ MORE

answered Feb 7, 2022 in Database by Vaani
• 7,070 points
1,443 views
0 votes
0 answers

SQL use CASE statement in WHERE IN clause

Can you use a case statement in ...READ MORE

Aug 9, 2022 in Database by Kithuzzz
• 38,000 points
697 views
+1 vote
1 answer

How to Insert date value in SQL table

Always use ANSI default string literal format for date i.e. YYYY-MM-DD like below. INSERT ...READ MORE

answered Feb 17, 2022 in Database by Neha
• 9,020 points
178,494 views
0 votes
1 answer

What is the difference between HAVING and WHERE in SQL?

HAVING: It is used to check after the aggregation ...READ MORE

answered Feb 17, 2022 in Database by Vaani
• 7,070 points
784 views
0 votes
0 answers

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL ...READ MORE

Aug 13, 2022 in Database by Kithuzzz
• 38,000 points
500 views
0 votes
0 answers

Left Join With Where Clause

I need to take all of the ...READ MORE

Aug 14, 2022 in Database by Kithuzzz
• 38,000 points
672 views
0 votes
1 answer

SQL Server: Multiple table joins with a WHERE clause

It matters whether you place the filter ...READ MORE

answered Sep 18, 2022 in Database by narikkadan
• 63,600 points
919 views
0 votes
1 answer

SQL keys, MUL vs PRI vs UNI

It denotes that the field is (a ...READ MORE

answered Sep 10, 2022 in Database by narikkadan
• 63,600 points
1,558 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP