Clever Geek Handbook

T-sql is the most preferred connection method

I have 2 sql query below. What is the most preferred method of getting table1.date> 0

Option1:

    select table1.id,table2.firstname, table2.lastname
    from table1 join table2 on table1.id = table2.id and table1.date > 0

Option2:

    select table1.id,table2.firstname, table2.lastname
    from table1 join table2 on table1.id = table2.id where table1.date > 0
+3


source to share


6 answers


I find the second option is easier as it is clearer and I know the data is being filtered using a sentence WHERE

.



+5


source


ALWAYS use the second option.

For INNER JOIN

they are equivalent.



The OUTER JOIN

two versions will return different result sets!

If you put this filter in the criteria ON

for OUTER JOIN

, you will have filtered records before applying JOIN

(and not after), which can give unexpected results.

+3


source


I would take option 2, it is always better to have a join by values ​​and later exclude the ones you don't need in the where clause.

+1


source 


Select 
    table1.id, table2.firstname, table2.lastname 
FROM table1 
INNER JOIN table2 ON table1.id = table2.id 
WHERE table1.date > 0

Must work.

0


source


Not a question, but an outer join, but as opposed to where the situation can be changed.

Consider the following. The second returns all the lines in docSVsys, while the first does not. Where the outer join was mostly included in the regular join.

    select COUNT(docSVsys.sID)
    from docSVsys 
    left outer join docMVtext 
    on docMVtext.sID = docSVsys.sID 
    where docMVtext.fieldID = 130

    select COUNT(docSVsys.sID)
    from docSVsys 
    left outer join docMVtext 
    on docMVtext.sID = docSVsys.sID 
    and docMVtext.fieldID = 130

I had some big requests that pulled a condition from where it was and resulted in the same request but a different plan.

0


source


Option 2 is syntactically correct and works fine. You are applying the filter correctly using the WHERE clause.

Option 1 is correct, but it is not easy to distinguish it from Option 2.

-1


source






More articles:


All Articles