dkb dkb - 1 year ago 63
SQL Question

Which of the following query is optimised/preferred

Consider two table
tableA and tableB





Goal: find driver_id and vehicle_id of allowed driver whose name is XYZ.

Query1:SELECT * FROM tableA,tableB {join-condition}{filter-condition}

SELECT tableA.driver_id,tableA.vehicle_id FROM tableA,tableB
tableA.driver_id=tableB.driver_id AND
tableA.vehicle_id=tableB.vehicle_id AND
tableA.driver_name='XYZ' AND
tableB.driver_name='XYZ' AND
tableA.is_allowed = 1

Query2:SELECT * FROM (SELECT * FROM tableA {filter-condition}) JOIN (SELECT * FROM tableB {filter-condition}) ON {join-condition}{filter-condition}

SELECT tableAA.driver_id,tableAA.vehicle_id FROM
(SELECT tableA.driver_id,tableA.vehicle_id from tableA WHERE tableA.driver_name='XYZ' AND
tableA.is_allowed = 1) as tableAA,
(SELECT tableB.driver_id,tableB.vehicle_id from tableB WHERE tableB.driver_name='XYZ') as tableBB
tableAA.driver_id=tableBB.driver_id AND

which type of query is readable, optimized and according to standard.

Answer Source

A correct version would look like this:

SELECT a.driver_id, a.vehicle_id
FROM tableA a JOIN
     tableB b
     ON a.driver_id = b.driver_id AND 
        a.vehicle_id = b.vehicle_id
WHERE a.driver_name = 'XYZ' AND
      b.driver_name = 'XYZ' AND
      a.is_allowed = 1;


  • JOIN is accepted as the right way to combine tables in the FROM clause. Simple rule: Never use commas in the FROM clause.
  • The ON clause should contain all predicates that contain columns from more than one table.
  • The use of table aliases is a preference that makes queries easier to write and to read.
  • You might want to use IN or EXISTS, because your query is not returning columns from TableB.
  • Do not use unnecessary subqueries in the FROM clause. In some databases (notably MySQL), this impedes the use of indexes and adds additional overhead for materialization of the intermediate table.

And, the answer to your question is that the first version is probably the optimized version (because it does not materialize subqueries unnecessarily). Neither version is preferred.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download