CODEWITHSUNDEEP
sqlsql-serverjoin
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

Different JOIN logic in SQL

I am basically trying to understand 2 different ways a join can be used

1) 

select * 
from Table1
LEFT JOIN Table2 ON Table1.ID = Table2.TableFK
WHERE Table2.Date >= '2004-01-01'

2)

select * 
from Table1
LEFT JOIN Table2 ON Table1.ID = Table2.TableFK
AND Table2.Date >= '2004-01-01'

Now i have an SQL fiddle demonstration http://sqlfiddle.com/#!9/5f3df6/3 to try and show this but the queries give me the same result. However, i know that these 2 queries have produced a different result in other scenarios.

I am finding it hard to understand them. What are the differences between these 3 queries and in which situations would i use them?

Upvotes: 0

Views: 441

Answers (4)

Zorkolot
Zorkolot

Reputation: 2017

The LEFT JOIN will return NULL for the 2nd table (right table) if there are no matching joining conditions.

This:

select * 
from Table1
LEFT JOIN Table2 ON Table1.ID = Table2.TableFK
WHERE Table2.Date >= '2004-01-01'

Returns nulls where Table1.ID does not match Table2.ID. If there is a match, it will exclude rows where Table2.Date < '2004-01-01'.

This:

select * 
from Table1
LEFT JOIN Table2 ON Table1.ID = Table2.TableFK
AND Table2.Date >= '2004-01-01'

Has the AND as part of the joining condition. This is a more restrictive LEFT JOIN than the first query. Like the first query, it returns nulls where Table1.ID does not match Table2.ID. Additionally Table2.Date must be less than '2004-01-01'. If both of these conditions are not met, table 2 columns will return as NULL. Nothing is excluded in the query (there is no WHERE clause). Hence, every row from table one either matches with Table2 by LEFT JOIN or the Table2 fields will be full of NULLS by LEFT JOIN.

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94904

An inner join does two things:

  1. It combines records
  2. It filters records

With JOIN Table2 ON Table1.ID = Table2.TableFK you say "give me the matching records from Table2". So you combine records. But you also say "dismiss records from Table1 that have no match". So you filter the results.

When adding either WHERE Table2.Date >= '2004-01-01' or AND Table2.Date >= '2004-01-01' you further limit the result. With WHERE the thought is "join records on their linking IDs and then remove records from the result where the date doesn't match". With adding AND to the ON clause on the other hand the thought is "join records that match the criteria only". But both do the same thing and the DBMS will use the same execution plan for both queries.

With outer joins it's different, because you combine records, but you don't filter. If there is no matching record in Table2, you still keep the record from Table1. You will say: "combine my Table1 record with zero to n maching Table2 records" where "matching" means matching the ID and the date range of course. If you put WHERE Table2.Date >= '2004-01-01' rather than adding the date criteria to the ON clause, you would dismiss all outer joined records, because in an outer-joined record (where the Table1 record had no match in Table2) Table2.Date would be null. And suddenly you would be filtering, thus turning your outer join into an inner join. This is an error often committed by beginners.

For that reason, some people suggest to put all criteria always in the ON clauses. Thus, if you want to turn your query into an outer join at some point, you won't fall into above mentioned trap. But well, there is also the order of tables in a FROM clause. The table you make first doesn't have an ON clause. You could turn this inner join

select * 
from Table1
JOIN Table2 ON Table1.ID = Table2.TableFK AND Table2.Date >= '2004-01-01'
WHERE Table1.Name LIKE 'S%';

easily into an outer join with 

LEFT JOIN Table2 ON Table1.ID = Table2.TableFK AND Table2.Date >= '2004-01-01'

But if you wanted to outer join Table1 to Table2 instead

RIGHT JOIN Table2 ON Table1.ID = Table2.TableFK AND Table2.Date >= '2004-01-01'

would fail and you'd have to re-write the query. (Which you should do anyway, because RIGHT JOIN is considered harder to read than LEFT JOIN and should hence be avoided.)

Having said this: For outer joins you must put all criteria in ON. For inner joins it's up to you where to put additional criteria.

UPDATE: I see you have altered your request and no longer ask about inner joins. Well, the answer for outer joins is also included in my answer, so I let it stand as is.

Upvotes: 3

Sherly Febrianti
Sherly Febrianti

Reputation: 1147

Query 1 :

It is joining table 1 and table 2 with same ID. After having the result, it will filter again the data and get the data which has Date '2004-01-01'.


Query 2 :

It is joining table 1 and table 2 to get the data with same ID and Date '2004-01-01'. (only data that match both filter will be thrown as result)


Query 3 :

Almost the same as Query 2. The difference is only between JOIN and LEFT JOIN.


You should find the difference between JOIN and LEFT JOIN in Google and any answered question in Stackoverflow. If what you are wondering, is not found, you can create a new issues and kindly tag me. I will help you as long as i know the answer.


* Cheers *

Upvotes: 0

RegBes
RegBes

Reputation: 569

If i understand what you are asking " what is the difference between Join and left join"

when you just say join it is what is called an inner join and it will only return records from the tables when the keys match

when you say left join it is an outer join what that means is that it will return all the rows from table1 and the matched ones from table 2 where there is no key match the fields for table2 will be all nulls

the reason you are getting identical results if your where condition Table2.Date >= '2004-01-01' is filtering out all the nulls in this case effectively turning the outer join into a inner join

Upvotes: 0

Related Questions