1

Alright, this is a relativly simple question that I just need confirmation on. I am pretty sure I know the answer, but I need expert validation because I have OCD.

Anyways, I am wondering what the difference is between just a simple Inner Join Statement and simply using the where clause to set the primary keys of the tables I am matching as = to each other.

Example: I wrote this

select a.CONTACT_ID, a.TRADE_REP as Rep_Trading_ID, p.CRD_NUMBER, c.FIRST_NAME, c.LAST_NAME
from dbo.REP_PROFILE p, dbo.REP_ALIAS a, dbo.CONTACT c
where 
c.CONTACT_ID = p.CONTACT_ID
and p.CONTACT_ID = a.TRADE_REP 
and a.PRIMARY_YN = 'y'

In my mind, this seems to perform the exact same query as if I were to have created and Inner Join between tables c and p and another inner join between tables p and a on Contact ID (Contact ID is my primary key for most everything)

Am I correct? Am I partially correct? Am I tottally hopelessly ignorant? Any validation will do

  • 1
    possible duplicate of [INNER JOIN ON vs WHERE clause](http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause) – Martin Smith Sep 12 '13 at 14:06

3 Answers3

1

Is the same!

look at these two tables:

CREATE TABLE table1 (
  id INT,
  name VARCHAR(20)
);

CREATE TABLE table2 (
  id INT,
  name VARCHAR(20)
);

The execution plan for the query using the inner join:

-- with inner join

EXPLAIN PLAN FOR
SELECT * FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id;

SELECT *
FROM TABLE (DBMS_XPLAN.DISPLAY);

-- 0 select statement
-- 1 hash join (access("T1"."ID"="T2"."ID"))
-- 2 table access full table1
-- 3 table access full table2

And the execution plan for the query using a WHERE clause.

-- with where clause

EXPLAIN PLAN FOR
SELECT * FROM table1 t1, table2 t2
WHERE t1.id = t2.id;

SELECT *
FROM TABLE (DBMS_XPLAN.DISPLAY);

-- 0 select statement
-- 1 hash join (access("T1"."ID"="T2"."ID"))
-- 2 table access full table1
-- 3 table access full table2

In my opinion is more readable to use JOIN.

JGutierrezC
  • 4,398
  • 5
  • 25
  • 42
  • I agree on the readability, and I prefer to use JOIN statements as it seems more politically correct. I was just wondering if the underlying action being performed was identical, and you sir have showed me exactly what I was looking for, Thanks! –  Sep 12 '13 at 14:47
  • @AdamW: Yes, it is. I was wrong by putting `EXPLAIN` in the answer because it is for MySQL. But always you have a doubt about how a query is executed in SQL Server, you should use "Display Estimated Execution Plan" in SQL Server Management Studio. That way you could see if it has a lack of indexes or if it is a wrong query. Is the best way to optimize a query. However, the answer is still the same, `JOIN` (not `LEFT JOIN` or any other variance) and `WHERE` works the same way. – JGutierrezC Sep 12 '13 at 14:58
0

Yes, using the format you provided works fine for INNER JOINS.

But what to do when you get to LEFT/RIGHT JOINS? Then you cannot use the joining conditions in the where clause like that.

Now if I am not mistaken, the old school syntax would be somethine like 

=* for LEFT JOIN

and

*= for RIGHT JOIN

You might wfind the following article a nice read (more specifically at the various JOIN types)

Join (SQL)

Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
  • Thank you for the response! I alsways love learning little bits of forgotten syntax. When using other joins I like to use a Printout of Visual SQL joins, which can be found here http://www.j0zf.com/uploads/dynamic_areas/lP2fs2414xx6pXdIbGir/1/Visual_SQL_JOINS_orig.jpg –  Sep 12 '13 at 14:45
0

Doing JOIN syntax is much easier to read (everything is obvious where it goes and things like that). It is also considered more flexible since changing JOIN to OUTER JOIN is extremely easy and much more maleable.

See this post for much more detail (it is MySQL, but the same issues arise/apply): INNER JOIN ON vs WHERE clause

Community
  • 1
  • 1
CrckrJack
  • 93
  • 5