4

I have about 6 months novice experience in SQL, TSQL, SSIS, ETL. As I find myself using JOIN statements more and more in my intern project I have been experimenting with the different JOIN statements. I wanted to confirm my findings. Are the following statements accurate pertaining to the conclusion of JOIN statements in SQL Server?:

1)I did a LEFT OUTER JOIN query and did the same query using JOIN which yielded the same results; are all JOIN statements LEFT OUTER associated in SQL Server?

2)I did a LEFT OUTER JOIN WHERE 2nd table PK (joined to) IS NOT NULL and did the same query using an INNER JOIN which yielded the same results; is it safe to say the the INNER JOIN statement will yield only matched records? and is the same as LEFT OUTER JOIN where joined records IS NOT NULL ?

The reason I'm asking is because I have been only using LEFT OUTER JOINS because that is what I was comfortable with. However, I want to eliminate as much code as possible when writing queries to be more efficient. I just wanted to make sure my observations are correct.

Also, are there any tips that you could provide on easily figuring out which JOIN statement is appropriate for specific queries? For instance, what JOIN would you use if you wanted to yield non-matching records?

Thanks.

J.S. Orris
  • 4,653
  • 12
  • 49
  • 89
  • 1
    I think [this answer](http://stackoverflow.com/a/17946222/1048425) or [this answer](http://stackoverflow.com/a/20298671/1048425) (to the same question) will explain all you need to know about different types of JOIN. If you are looking to make your code less verbose `LEFT OUTER JOIN` is synonymous with `LEFT JOIN` (a `LEFT INNER JOIN` makes no sense), and `INNER JOIN` is synonymous with just `JOIN` – GarethD May 16 '14 at 22:46
  • 1
    Or [this answer](http://stackoverflow.com/a/38578/1048425) and [this answer](http://stackoverflow.com/a/16598900/1048425) (also to the same question). If your LEFT OUTER JOIN and INNER JOIN yield the same results it is by chance, and there are no results missing from your joined table. – GarethD May 16 '14 at 22:56
  • Haha. Seen someone +1 this question that I asked so long ago. Looking back, I am so much far advanced in SQL now I have to laugh at this. – J.S. Orris Oct 15 '17 at 02:50

2 Answers2

2

A join or inner join (same thing) between table A and table B on, for instance, field1, would narrow in on all rows of table A and B sharing the same field1 value.

A left outer join between A and B, on field1, would show all rows of table A, and only those rows of table B that have a field1 existing in table A.

Where the rows of field1 on table A have a field1 value that doesn't exist in table B, the table B value would show null for field1, but the row of table A would be retained because it is an outer join. These are rows that wouldn't show up in a join which is an implied inner join.

If you get the same results doing a join between table A and table B as you do a left outer join between table A and B, then whatever fields you're joining on have values that exist in both tables. No value for any of the joined fields in A or B exist exclusively in A or B, they all exist in both A and B.

It is also possible you're putting criteria into the where clause that belongs in the on clause of the outer join, which may be causing your confusion. In my example above of tables A and B, where A is being left outer joined with B, you would put any criteria related to table B in the on clause, not the where clause, otherwise you would essentially be turning the outer join into an inner join. For example if you had b.field4 = 12 in the WHERE clause, and table B didn't have a match with A, it would be null and that criteria would fail, and it'd no longer come back even though you used a left outer join. That may be what you are referring to.

Brian DeMilia
  • 13,103
  • 1
  • 23
  • 33
  • Thank you for the concise answer...i have been evaluating `SQL JOIN` venn diagrams and understand it better now. – J.S. Orris May 18 '14 at 21:59
1

JOIN's are mapped to 'INNER JOIN' by default

Keith John Hutchison
  • 4,955
  • 11
  • 46
  • 64
  • Why did my `LEFT OUTER JOIN` produce the same results as `JOIN` then? – J.S. Orris May 16 '14 at 22:45
  • It would relate to your current data set. A Left outer join is _not_ an [Inner] join. Try adding some records in the left table that have no matching keys in the right table and you'll see the difference. http://en.wikipedia.org/wiki/Join_(SQL) – Keith John Hutchison May 16 '14 at 22:53