SQL JOIN - WHERE clause vs ON clause

0 votes
What is the difference and what should go in each?
Sep 14 in Database by Kithuzzz
• 12,240 points
25 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 by narikkadan
• 20,880 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,240 points
584 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 in Database by Neha
• 8,920 points
147 views
0 votes
1 answer

SQL Switch/Case in 'where' clause

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

answered Feb 7 in Database by Vaani
• 7,020 points
117 views
0 votes
0 answers

SQL use CASE statement in WHERE IN clause

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

Aug 9 in Database by Kithuzzz
• 12,240 points
36 views
0 votes
1 answer

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 in Database by Neha
• 8,920 points
12,802 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 in Database by Vaani
• 7,020 points
89 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 in Database by Kithuzzz
• 12,240 points
24 views
0 votes
0 answers

Left Join With Where Clause

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

Aug 14 in Database by Kithuzzz
• 12,240 points
21 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 in Database by narikkadan
• 20,880 points
12 views
0 votes
1 answer

SQL WHERE ID IN (id1, id2, ..., idn)

Option 1 is the only good solution. Why? Option ...READ MORE

answered Sep 12 in Database by narikkadan
• 20,880 points
39 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