StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If you put filter criteria in the WHERE clause, and that criteria dictates conditions against the "outer" tables of a join, you effectively turn that outer join into an inner join. Filter criteria against an outer join table belongs in the join, not in the where.</p> <p>How many rows does this version yield?</p> <pre><code>SELECT P.*, X.* FROM Table1 P LEFT OUTER JOIN Table2 X on P.Id = X.id and P.name = X.name and P.age = X.age AND X.bar = 1 and X.name ='value'; </code></pre> <p>Theoretically, this should yield >= the number of rows in your first statement, and = the number of rows in your second.</p> <p>If you want to compare this with your existing result, you can do:</p> <pre><code>SELECT P.*, X.* FROM Table1 P LEFT OUTER JOIN Table2 X on P.Id = X.id and P.name = X.name and P.age = X.age AND X.bar = 1 and X.name ='value' EXCEPT SELECT P.* and X.* FROM Table1 P LEFT OUTER JOIN (SELECT * FROM Table2 WHERE X.bar = 1 and X.name ='value') X on P.Id = X.id and P.name = X.name and P.age = X.age; </code></pre> <p>If you get no results, they're the same. You could also do it the opposite way:</p> <pre><code>SELECT P.*, X.* FROM Table1 P LEFT OUTER JOIN Table2 X on P.Id = X.id and P.name = X.name and P.age = X.age AND X.bar = 1 and X.name ='value' INTERSECT SELECT P.* and X.* FROM Table1 P LEFT OUTER JOIN (SELECT * FROM Table2 WHERE X.bar = 1 and X.name ='value') X on P.Id = X.id and P.name = X.name and P.age = X.age; </code></pre> <p>If you get 5000 rows (or whatever the exact row count is for the latter query by itself), then the result sets are the same.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload