CODEWITHSUNDEEP
sqloracle11goracle-sqldeveloper
arun
arun

Reputation: 791

Differences in these 2 query

Is this 2 query different? Im getting different results in Oracle SQL, can you explain the difference?

***First Query***
SELECT *
FROM 
TABLE1,
TABLE2
WHERE TABLE1.COL_ID   = TABLE2.COL_ID
AND TABLE1.COL1 = 'ABC'
AND TABLE2.COL1 = 'ABC'
AND TABLE1.COL2 = 2015
AND TABLE1.COL3 = 'X';

***Second Query***
SELECT *
FROM 
TABLE1,
TABLE2,
TABLE3
WHERE TABLE1.COL_ID   = TABLE2.COL_ID
AND TABLE1.COL1 = 'ABC'
AND TABLE2.COL1 = 'ABC'
AND TABLE1.COL2 = 2015
AND TABLE1.COL3 = 'X';

Upvotes: 0

Views: 42

Answers (2)

54l3d
54l3d

Reputation: 3973

Suppose you make this query:

Select * from 
 myTable1 ,myTable2

You will get (myTable1 rows number) x (myTable2 rows number) because without joining condition you will combine every rows from first table with every rows from second table, this is called cartesian product, and the join is cross join.

In your case the first query contains an inner join because of use of WHERE TABLE1.COL_ID = TABLE2.COL_ID, in the second query, the result of the first join is cross joined with table TABLE3.

Upvotes: 1

BrianAtkins
BrianAtkins

Reputation: 1349

Yes, the SQL processor assumes that you are implying a JOIN all three tables: table1,table2,table3

You are not required to explicitly state a join clause, if you don't the SQL engine will handle it.

When you use a comma to separate two (or more) table names what you are intending is the cartesian product. Every row of the 'left' table will be 'matched' (concatenated) with that of the right table.

Now if you write something in the where clause, it's like putting a condition on this 'concatenation' telling which rows to 'concatenate' with which rows.

This is actually "joining" the rows :) and hence the join keyword that helps provide a more readable syntax and is more understandable that you 'indeed' want to join on some common values.

Upvotes: 2

Related Questions