CODEWITHSUNDEEP
sqlsql-serverjoininner-join
user2623972
user2623972

Reputation:

SQL Primary Key/Table Joining

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

Upvotes: 1

Views: 2381

Answers (3)

CrckrJack
CrckrJack

Reputation: 93

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

Upvotes: 0

JGutierrezC
JGutierrezC

Reputation: 4513

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.

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166396

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)

Upvotes: 0

Related Questions