CODEWITHSUNDEEP
sqloracle-databasejoin
user2010955
user2010955

Reputation: 4011

SQL Join between tables with conditions

I'm thinking about which should be the best way (considering the execution time) of doing a join between 2 or more tables with some conditions. I got these three ways:

FIRST WAY:

select * from 
TABLE A inner join TABLE B on A.KEY = B.KEY
where 
B.PARAM=VALUE

SECOND WAY

select * from 
TABLE A inner join TABLE B on A.KEY = B.KEY 
and B.PARAM=VALUE

THIRD WAY

select * from
TABLE A inner join (Select * from TABLE B where B.PARAM=VALUE) J ON A.KEY=J.KEY

Consider that tables have more than 1 milion of rows.

What your opinion? Which should be the right way, if exists?

Upvotes: 4

Views: 16333

Answers (3)

tooba jalali
tooba jalali

Reputation: 86

Usually putting the condition in where clause or join condition has no noticeable differences in inner joins.
If you are using outer joins ,putting the condition in the where clause improves query time because when you use condition in the where clause of left outer joins, rows which aren't met the condition will be deleted from the result set and the result set becomes smaller. But if you use the condition in join clause of left outer joins ,no rows deletes and result set is bigger in comparison to using condition in the where clause. for more clarification,follow the example.

create table A

( ano NUMBER,

aname VARCHAR2(10),

rdate DATE )

----A data

insert into A select 1,'Amand',to_date('20130101','yyyymmdd') from dual; commit;

insert into A select 2,'Alex',to_date('20130101','yyyymmdd') from dual; commit;

insert into A select 3,'Angel',to_date('20130201','yyyymmdd') from dual;

commit;

create table B

( bno NUMBER,

bname VARCHAR2(10),

rdate DATE )

insert into B select 3,'BOB',to_date('20130201','yyyymmdd') from dual; commit;

insert into B select 2,'Br',to_date('20130101','yyyymmdd') from dual; commit;

insert into B select 1,'Bn',to_date('20130101','yyyymmdd') from dual; commit;

first of all we have normal query which joins 2 tables with each other:

select * from a inner join b on a.ano=b.bno

the result set has 3 records. now please run below queries: 

select * from a inner join b on a.ano=b.bno and a.rdate=to_date('20130101','yyyymmdd')

    select * from a inner join b on a.ano=b.bno where a.rdate=to_date('20130101','yyyymmdd')

as you see above results row counts have no differences,and According to my experience there is no noticeable performance differences for data in large volume.

please run below queries:

select * from a left outer  join b on a.ano=b.bno and a.rdate=to_date('20130101','yyyymmdd')

in this case,the count of output records will be equal to table A records.

select * from a left outer  join b on a.ano=b.bno where a.rdate=to_date('20130101','yyyymmdd')

in this case , records of A which didn't met the condition deleted from the result set and as I said the result set will have less records(in this case 2 records).

According to above examples we can have following conclusions:

1-in case of using inner joins, there is no special differences between putting condition in where clause or join clause ,but please try to put tables in from clause in order to have minimum intermediate result row counts: (http://www.dba-oracle.com/art_dbazine_oracle10g_dynamic_sampling_hint.htm)

2-In case of using outer joins,whenever you don't care of exact result row counts (don't care of missing records of table A which have no paired records in table B and fields of table B will be null for these records in the result set),put the condition in the where clause to delete a set of rows which aren't met the condition and obviously improve query time by decreasing the result row counts.

but in special cases you HAVE TO put the condition in the join part.for example if you want that your result row count will be equal to table 'A' row counts(this case is common in ETL processes) you HAVE TO put the condition in the join clause.

3-avoiding subquery is recommended by lots of reliable resources and expert programmers.It usually increase the query time and you can use subquery just when its result data set is small.

I hope this will be useful:)

Upvotes: 3

ChrisProsser
ChrisProsser

Reputation: 13088

You need to look at the execution plans for the queries to judge which is the most computationally efficient. As pointed out in the comments you may find they are equivalent. Here is some information on Oracle execution plans. Depending on what editor / IDE you use the may be a shortcut for this e.g. F5 in PL/SQL Developer.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311308

1M rows really isn't that much - especially if you have sensible indexes. I'd start off with making your queries as readable and maintainable as possible, and only start optimizing if you notice a perforamnce problem with the query (and as Gordon Linoff said in his comment - it's doubtful there would even be a difference between the three).

It may be a matter of taste, but to me, the third way seems clumsy, so I'd cross it out. Personally, I prefer using JOIN syntax for the joining logic (i.e., how A and B's rows are matched) and WHERE for filtering (i.e., once matched, which rows interest me), so I'd go for the first way. But again, it really boils down to personal taste and preferences.

Upvotes: 0

Related Questions