-12

In SQL Server, what is the difference between Left and OUTER JOIN?

Nitin Jain
  • 129
  • 1
  • 14

2 Answers2

7

I'm answering this in the spirit of welcoming a new user to the site.

OUTER APPLY can implement anything that LEFT JOIN can do -- and much more.

JOIN is the original operation in relational databases. It is how SQL is taught and helps to learn about thinking in terms of sets -- JOINs are generally related to some subset of the Cartesian product of two sets.

APPLY implements what is technically called a "lateral join". I view these as variations on correlated subqueries. Essentially, these are correlated subqueries that can return more than one column and more than one row.

My understanding of relational databases is firmly grounded in JOIN. When teaching SQL, that is how I would explain relationships between tables.

Perhaps one day, APPLY (or LATERAL which is used in other databases) will be the default method for teaching SQL. However, I don't think that APPLY helps people understand set-based thinking, so I prefer learning and using JOIN first, and then using APPLY in the many other cases where it is useful.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

First, I do not really know which is the default OUTER JOIN in T-SQL (I'd bet FULL), but in any case, I think it is unclear - it is best to use LEFT, RIGHT, OR FULL JOIN explicitly when you need one.

JOIN joins tables. Already existent ones or subqueries.

APPLY applies (duh) a table-valued function-equivelant in every row of the (left) table. Some interesting cases are:

  1. Apply an existing actual function: CROSS/OUTER APPLY dbo.your_function_name
  2. You could use a SELECT (some fields) without a FROM in order to create aliases for the fields - that makes your code look neat
  3. You can use a SELECT with a FROM to make a subquery. This is the same as a join

CROSS APPLY is a superset of INNER JOIN, and OUTER APPLY is a superset of LEFT. You can't have a function be applied to nothing, as is the case with RIGHT or FULL joins.

George Menoutis
  • 6,894
  • 3
  • 19
  • 43
  • 1
    Arggh. There is no default `outer join` (in any database as far as I know). `cross apply` and `outer apply` are not *equivalent* to any joins. They are supersets of the functionality. – Gordon Linoff Aug 23 '18 at 13:11
  • I agree, thus I'll edit, although I felt equivelant is simpler – George Menoutis Aug 23 '18 at 13:15