2

I always assumed that a statement of the following form 

SELECT * FROM A LEFT JOIN B ON (A.column1 = B.column2 AND B.column2 = 12321)

is always equivalent to 

SELECT * FROM A LEFT JOIN B ON (A.column1 = B.column2)
WHERE B.column2 = 12321

In more general terms:

SELECT * FROM A LEFT JOIN B ON (FOREIGN-KEY AND FILTER_ON(B))
WHERE FILTER_ON(A)

should be equivalent to

SELECT * FROM A LEFT JOIN B ON (FOREIGN-KEY)
WHERE FILTER_ON(A) AND FILTER_ON(B)

This does not seem to be the case... The first type gives a larger number of resulting rows than the second.

Question: In which cases is my assumption wrong?

mathtec
  • 191
  • 1
  • 6
  • 3
    The best way to learn SQL is by programming. Create the tables, insert some data and see if there's a difference or not! (Hint: There is. Put right side table conditions in the ON clause, otherwise you'll get regular inner join result.) – jarlh Sep 07 '18 at 07:30
  • The best way to understand is: In the query are only records displayed if they match the where condition - on the other hand, if there is no match in the left join condition, the record is displayed, but with an empty row for the left joined table... (If you use a normal join (no left join) then the two statements are equivalent, but not for left, right or full joins.) – Radagast81 Sep 07 '18 at 08:19
  • @jarlh I don't think this question warrants the go-and-learn-sql-first comment. Mathtec, welcome aboard! – Beginner Sep 07 '18 at 08:39

2 Answers2

8

No. For an outer join, they are not equivalent.

A left join keeps all rows in the first table, regardless of whether the on clause evaluates to true, false, or NULL. So, a condition in the on on only the first table has no effect.

If you want filtering on the first table in a left join, put the conditions in the where. Conditions on the second table generally go in the on clause.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you very much for your response! I have updated the question and I still do not understand why the filtering-condition on the second table B has different effects on the results in the join-condition. – mathtec Sep 07 '18 at 08:09
2

In your first Query you written LEFT JOIN that means left side tables all data.

Query:1
SELECT * FROM A LEFT JOIN B ON (FOREIGN-KEY AND FILTER_ON(A) AND FILTER_ON(B)) Query:2
SELECT * FROM A LEFT JOIN B ON (FOREIGN-KEY) WHERE FILTER_ON(A) AND FILTER_ON(B)
In Query:1, Having condition in LEFT JOIN that means still we have LEFT side tables all data and NULL values with Table B when condition getting false.

If you write condition in where clause(Query:2) for table B then there will be data mismatch in result of Query 1 & Query 2. because data will be filtered out in query result by condition.

JERRY
  • 1,165
  • 1
  • 8
  • 22