SQL Server LEFT JOIN and WHERE clause

sql sql-server left-join
Felipe Miosso picture Felipe Miosso · Oct 28, 2013 · Viewed 68.3k times · Source

Here is my code

SELECT ID, Name, Phone 
FROM Table1 
LEFT JOIN Table2 ON Table1.ID = Table2.ID
WHERE Table1.ID = 12 AND Table2.IsDefault = 1

The problem happens when Table2 is null, so, the query returns nothing.

How do I leave the last part of the query AND Table2.IsDefault = 1 optional?

I've tried to short circuit the query using OR but I've found out that it works different than C#

Answer

t-clausen.dk picture t-clausen.dk · Oct 28, 2013 
SELECT ID, Name, Phone 
FROM Table1 
LEFT JOIN Table2 
ON Table1.ID = Table2.ID AND Table2.IsDefault = 1
WHERE Table1.ID = 12

Related questions

Difference between left join and right join in SQL Server

I know about joins in SQL Server. For example. There are two tables Table1, Table2. Their table structures are the following. create table Table1 (id int, Name varchar (10)) create table Table2 (id int, Name varchar (10)) Table1 data as follows: Id …

Read More
SQL Server Left Join With 'Or' Operator

I have a four tables, TopLevelParent, two mid level tables MidParentA and MidParentB, and a Child table which can have a parent of MidParentA or MidParentB (One or the other midParent must be in place). Both mid level tables have …

Read More
Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON?

I'm experiencing a very confusing situation that makes me question all my understanding of joins in SQL Server. SELECT t1.f2 FROM t1 LEFT JOIN t2 ON t1.f1 = t2.f1 AND cond2 AND t2.f3 > something Does not …

Read More