What if i dont use Join Keyword in query?

Question

I have a query where i am retrieving data from more than two tables. I am using the filter criteria in where clause but not using any join keyword

select
    d.proc_code,
    d.dos,
    s.svc_type
from 
    claim_detail d, h_claim_hdr hh, car_svc s 
where 
    d.bu_id="$inp_bu_id" 
and
    hh.bu_id="$inp_bu_id" 
and 
    s.bu_id="$inp_bu_id" 
and 
    d.audit_nbr="$inp_audit_nbr" 
and 
    hh.audit_nbr="$inp_audit_nbr"
and 
    d.audit_nbr=hh.audit_nbr 
and 
    s.car_svc_nbr=hh.aut_nbr

Is there a better way of writing this?

use a join for better efficiency or to make your query more readable, but for quick queries on small amounts of data doing it this way is quick and it works. using no join just defaults to join with no "on" section. — Angus Walker, Nov 07 '13 at 18:39

score 4 · Answer 1 · answered Nov 07 '13 at 18:39

Although you are not using a JOIN keyword, your query does perform a JOIN.

A more "modern" way of writing your query (i.e. one following the ANSI SQL standard) would be as follows:

select
    d.proc_code,
    d.dos,
    s.svc_type
from 
    claim_detail d
join
    h_claim_hdr hh on d.audit_nbr=hh.audit_nbr 
join
    car_svc s on s.car_svc_nbr=hh.aut_nbr
where 
    d.bu_id="$inp_bu_id" 
and
    hh.bu_id="$inp_bu_id" 
and 
    s.bu_id="$inp_bu_id" 
and 
    d.audit_nbr="$inp_audit_nbr" 
and 
    hh.audit_nbr="$inp_audit_nbr"

Note that this is simply a modern syntax. It expresses the same query, and it will not impact the performance.

Note that in order for a row to appear in the output of this query, the corresponding rows must exist in all three queries (i.e. it's an inner join). If you would like to return rows of claim_detail for which no h_claim_hdr and / or car_svc existed, use left outer join instead.

score 0 · Answer 2 · answered Nov 07 '13 at 18:45

0

A comma in the from clause is essentially the same as a cross join. You really don't want to use a cross join, unless you really know what you are doing.

Proper join syntax has several advantages. The most important of which is the ability to express other types of joins easily and compatibly across databases.

Most people would probably find this version easier to follow and maintain:

select d.proc_code, d.dos, s.svc_type
from  claim_detail d join
      h_claim_hdr hh
      on d.bu_id = hh.bu_id and d.audit_nbr = hh.audit_nbr
      car_svc s 
      on d.bu_id = s.bu_id and s.car_svc_nbr = hh.aut_nbr
where d.bu_id = "$inp_bu_id" 
      d.audit_nbr = "$inp_audit_nbr";

answered Nov 07 '13 at 18:45

Gordon Linoff

1,242,037
58
646
786

The assertion here is not necessarily correct. If you provide join criteria in the `where` clause, the comma results an inner join. If you do not provide any join criteria in the `where` clause, then you end up with a cross join. – Ian Bjorhovde Nov 08 '13 at 15:09
@IanBjorhovde . . . You are confusing different concepts. An inner join *is* a filtered cross join, by definition. In general, database engines optimize cross joins filtered by appropriate equality conditions to use inner join algorithms. However, a query with a comma in the `from` *expresses* a cross join, regardless of subsequent filtering. – Gordon Linoff Nov 16 '13 at 22:55

score 0 · Answer 3 · answered Nov 07 '13 at 18:47

Using the WHERE clause instead of the JOIN keyword is essentially a different syntax for doing a join. I believe it is called Theta syntax, where using the JOIN clause is called ANSI syntax.

I believe ANSI syntax is almost universally recommended, and some databases require ANSI syntax for outer JOINs.

score -1 · Answer 4 · edited May 23 '17 at 12:21

-1

If you do not use JOIN it will be an implicit inner join. As is in your example with the join criteria on your WHERE clause. So you could me missing records. Lets say you want all records from the first table even if there is not a corresponding record in the second. Your current code would only return the records from the first table that have a matching record in the second.

Joins

edited May 23 '17 at 12:21

Community

1
1

answered Nov 07 '13 at 18:38

CodeMonkey

1,087
2
15
24

This is incorrect. A `','` is not equivalent to an inner join. – Gordon Linoff Nov 07 '13 at 18:46
Thanks for your replies. It answers my question. I want to have implicit inner join only where in if records dont exist in first table then they should not showup. – user2946211 Nov 07 '13 at 20:30
Gordon is right, just listing two tables will join them, but no inner join. It will be a Cartesian Product (each line of one table with each line of the other table). That will be quite a few rows. – Peter Schuetze Nov 07 '13 at 20:30
1

@PeterSchuetze and @GordonLinoff: As long as there are join criteria provided in the `where` clause, this syntax results in an inner join. – Ian Bjorhovde Nov 08 '13 at 15:11
@IanBjorhovde: Correct. The emphasis is on *as long as you provide join criteria*. So I would not call it implicit join. – Peter Schuetze Nov 08 '13 at 15:15

What if i dont use Join Keyword in query?

4 Answers4