1

I am following along here:

https://www.tutorialspoint.com/sql/sql-using-joins.htm

The Cartesian join is specified by a select statement with no special notation. That's reasonable, since you take all possible ordered n-tuple combinations out of a SELECT statement. That's what SELECT does.

SELECT table1.column1, table2.column2...
FROM  table1, table2;

If you want to impose conditions, you can use WHERE to ask for them. So I would expect there to be no special keyword for INNER JOIN because you can just use the cartesian join, and then restrict to WHERE table1.column1=table2.column2.

Instead, notation exists like this:

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

My question is: why?

Siyual
  • 16,415
  • 8
  • 44
  • 58
Jeff
  • 125
  • 7
  • Because the implicit `JOIN` syntax (commas in the `WHERE` clause) was deprecated [**over 25 years ago**](http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt) with the ANSI-92 standard. It makes for a much clearer `JOIN` notation - especially for `OUTER JOIN`s. – Siyual Feb 13 '17 at 21:47
  • https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins – dfundako Feb 13 '17 at 21:47
  • I think you will find anwser on below link http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins – Tajinder Feb 13 '17 at 21:48
  • In addition to it being a cleaner, clearer, syntax, it also helps protect against doing a cartesian `JOIN` in the first place. Forcing everything to be a cartesian `JOIN` (or `CROSS JOIN`) and *then* filtering the results in the `WHERE` clause is bound to lead to an accidental `CROSS JOIN` at some point. And to re-iterate a previous point, this was deprecated *over 25 years ago*! Some DBMS products do not even support `OUTER JOIN`s done in this syntax anymore (e.g. `SQL Server 2012` and later). Just stick with explicit `JOIN`s. – Siyual Feb 13 '17 at 21:58
  • Possible duplicate of [Explicit vs implicit SQL joins](http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins) – jjj Feb 13 '17 at 22:00
  • 1
    The comma IS NOT reasonable for a Cartesian product. `CROSS JOIN` is now the standard and should be used. – Gordon Linoff Feb 13 '17 at 22:25

2 Answers2

3

Listing tables in the FROM clause and putting all filters (including join conditions) in the WHERE clause is the old way to do it. There was a time when this approach (with a bit of extended syntax) was even used for outer joins (on Oracle at least).

But the standard approach now is to use explicit joins. So why?

When you have multiple joins, and also possibly filter conditions that are not part of any join, it is more readable to use the explicit join syntax.

Consider:

SELECT emp.ID, mgr.ID, dept.NAME, asset.TAG_NO
  FROM employee emp, employee mgr, department dept, asset
 WHERE emp.HIRE_DT > :grandfather_date
   AND emp.MGR_ID = mgr.ID
   AND dept.NAME != 'IT'
   AND asset.EMP_ID = mgr.ID
   AND asset.TYPE_CD == 'D'

Now, in trouble-shooting this query, you may need to quickly assess things like: Did I join all tables correctly? If a row shows employee A and manager B, to whom is the asset on that row assigned? Who has to be in IT to be excluded from the report?

Carefully organizing the code in the query can help you answer these questions more quickly and reliably. (And this is a pretty modest-sized query when we get down to it; the bigger the query, the more this matters.)

Well, the current standard join syntax imposes some of that sane structure you should want:

SELECT emp.ID, mgr.ID, dept.NAME, asset.TAG_NO
  FROM            employee emp
       INNER JOIN employee mgr
               ON emp.MGR_ID = mgr.ID
       INNER JOIN department dept
               -- OOPS! This was missing above!
               ON emp.DEPT_ID = dept.ID
       INNER JOIN asset
               ON asset.EMP_ID = mgr.ID
 WHERE emp.HIRE_DT > :grandfather_date
   AND dept.NAME != 'IT'
   AND asset.TYPE_CD == 'D'

The fact that a predicate is, or is not, a join condition is now explicit. You know which join is constrained by a given predicate, so you know if you have constraints on all joins (and in fact, unless you specifically use CROSS JOIN syntax you're required to... because that's almost always correct). If you need to know "whose asset (employee or manager) are we talking about", you know to go straight to the join condition for the asset table to check it out.

You still might have to think about organizing your where clause if you have many non-join conditions; and you still have to format your code. But if you do these things, there will be much less reason for the on-call support guy to curse your name.

Mark Adelsberger
  • 42,148
  • 4
  • 35
  • 52
0

I think you just need to understand what are implicit and explicit join and difference between them.

Below mentioned links will help you.

http://sql-plsql.blogspot.in/2011/02/explicit-implicit-sql-joins.html

Explicit vs implicit SQL joins

http://chase-seibert.github.io/blog/2009/04/17/mixing-and-matching-implicit-and-explicit-joins.html

Community
  • 1
  • 1
Tajinder
  • 2,248
  • 4
  • 33
  • 54