CODEWITHSUNDEEP
sqloracle-database
user3838132
user3838132

Reputation: 31

Is there a better way to write this Oracle SQL query?

I have been using Oracle SQL for around 6 months so still a beginner. I need to query the database to get information on all items on a particular order (order number is via $_GET['id']).

I have come up with the below query, it works as expected and as I need but I do not know whether I am over complicating things which would slow the query down at all. I understand there are a number of ways to do a single thing and there may be better methods to write this query since I am a beginner.

I am using Oracle 8i (due to this is the version an application we use is supplied with) so I believe that some JOIN etc. are not available in this version, but is there a better way to write a query such as the below?

SELECT auf_pos.auf_pos,
       (SELECT auf_stat.anz
        FROM   auf_stat
        WHERE  auf_stat.auf_pos = auf_pos.auf_pos
               AND auf_stat.auf_nr = ".$_GET['id']."),
       (SELECT auf_text.zl_str
        FROM   auf_text
        WHERE  auf_text.zl_mod = 0
               AND auf_text.auf_pos = auf_pos.auf_pos
               AND auf_text.auf_nr = ".$_GET['id']."),
       (SELECT glas_daten_basis.gl_bez
        FROM   glas_daten_basis
        WHERE  glas_daten_basis.idnr = auf_pos.glas1),
       (SELECT lzr_daten.lzr_breite
        FROM   lzr_daten
        WHERE  lzr_daten.lzr_idnr = auf_pos.lzr1),
       (SELECT glas_daten_basis.gl_bez
        FROM   glas_daten_basis
        WHERE  glas_daten_basis.idnr = auf_pos.glas2),
       auf_pos.breite,
       auf_pos.hoehe,
       auf_pos.spr_jn
FROM   auf_pos
WHERE  auf_pos.auf_nr = ".$_GET['id']."

Thanks in advance to any Oracle gurus that could help this beginner out!

Upvotes: 0

Views: 75

Answers (1)

StevieG
StevieG

Reputation: 8709

You could rewrite it using joins. If your subselects aren't expected to return any NULL values, then you can use INNER JOINS:

SELECT auf_pos.auf_pos,
       auf_stat.anz,
       auf_text.zl_str,
       glas_daten_basis.gl_bez,
       lzr_daten.lzr_breite,
       glas_daten_basis.gl_bez,
       auf_pos.breite,
       auf_pos.hoehe,
       auf_pos.spr_jn
FROM   auf_pos
INNER JOIN auf_stat ON auf_stat.auf_pos = auf_pos.auf_pos AND auf_stat.auf_nr = ".$_GET['id'].")
INNER JOIN auf_text ON auf_text.zl_mod = 0 AND auf_text.auf_pos = auf_pos.auf_pos AND auf_text.auf_nr = ".$_GET['id'].")
INNER JOIN glas_daten_basis ON glas_daten_basis.idnr = auf_pos.glas1
INNER JOIN lzr_daten ON lzr_daten.lzr_idnr = auf_pos.lzr1
INNER JOIN glas_daten_basis ON glas_daten_basis.idnr = auf_pos.glas2

Or if there are cases where you wouldn't have matches on all the tables, you could replace the INNER joins with LEFT OUTER joins:

SELECT auf_pos.auf_pos,
       auf_stat.anz,
       auf_text.zl_str,
       glas_daten_basis.gl_bez,
       lzr_daten.lzr_breite,
       glas_daten_basis.gl_bez,
       auf_pos.breite,
       auf_pos.hoehe,
       auf_pos.spr_jn
FROM   auf_pos
LEFT OUTER JOIN auf_stat ON auf_stat.auf_pos = auf_pos.auf_pos AND auf_stat.auf_nr = ".$_GET['id'].")
LEFT OUTER JOIN auf_text ON auf_text.zl_mod = 0 AND auf_text.auf_pos = auf_pos.auf_pos AND auf_text.auf_nr = ".$_GET['id'].")
LEFT OUTER JOIN glas_daten_basis ON glas_daten_basis.idnr = auf_pos.glas1
LEFT OUTER JOIN lzr_daten ON lzr_daten.lzr_idnr = auf_pos.lzr1
LEFT OUTER JOIN glas_daten_basis ON glas_daten_basis.idnr = auf_pos.glas2

Whether or not you see any performance gains is debatable. As I understand it, the Oracle query optimizer should take your query and execute it with a similar plan to the join queries, but this is dependent on a number of factors, so the best thing to do it give it a try..

Upvotes: 4

Related Questions