WWW.APPSERVGRID.COM
https://www.art2dec.co/fort/

18 rules to write good SQL queries.
https://www.art2dec.co/fort/viewtopic.php?f=21&t=7386		 Page 1 of 1
Author:  admin [ Wed Jan 30, 2013 5:02 pm ]
Post subject:  18 rules to write good SQL queries.
*************************
Using WHERE:
*************************

1)
Worse:
------------------------------------------------
SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE SUBSTR( customer_n, 1, 3) = 'Luk';
------------------------------------------------
Better:
------------------------------------------------
SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE customer_n LIKE 'Luk%';
-------------------------------------------------

2)
Worse:
-------------------------------------------------
var ls_n VARCHAR2( 20)
exec ls_n := 'Lukyanov'

SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE customer_n = NVL( :ls_n, customer_n);
--------------------------------------------------
Better:
--------------------------------------------------
var ls_n VARCHAR2( 20)
exec ls_n := 'Lukyanov'

SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE customer_n LIKE NVL( :ls_n, '%');
---------------------------------------------------

3)
Worse:
---------------------------------------------------
SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE TRUNC( doc_dd) = TRUNC( sysdate);
---------------------------------------------------
Better (than more "9" than more preciously):
---------------------------------------------------
SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE doc_dd BETWEEN TRUNC( sysdate)
AND TRUNC( sysdate) + 0.9999;
----------------------------------------------------

4)
Worse:
----------------------------------------------------
SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE customer_n || doc_nd = 'Lukyanov29';
----------------------------------------------------
Better:
----------------------------------------------------
SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE customer_n = 'Lukyanov'
AND doc_nd = ‘29';
-----------------------------------------------------

5)
Worse:
-----------------------------------------------------
SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE total_doc + 500 < 2500;
------------------------------------------------------
Better:
------------------------------------------------------
SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE total_doc < 2000;
------------------------------------------------------

6)
Worse:
-------------------------------------------------------
SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE total_doc != 0;
-------------------------------------------------------
Better:
-------------------------------------------------------
SELECT doc_dd, customer_n, total_doc
FROM exdoc
WHERE total_doc > 0;
(when values of total_doc are positive)
--------------------------------------------------------

************************
Using HAVING
************************

7)
Worse:
---------------------------------------------------------
SELECT a.customer_n
, AVG( b.quant)
FROM exdoc a
, expos b
WHERE a.doc_nd = b.doc_nd
GROUP BY a.customer_n
HAVING customer_n != 'Lukyanov'
AND customer_n != 'Ivanov';
-----------------------------------------------------------
Better:
-----------------------------------------------------------
SELECT a.customer_n
, AVG( b.quant)
FROM exdoc a
, expos b
WHERE a.doc_nd = b.doc_nd
AND customer_n != 'Lukyanov'
AND customer_n != 'Ivanov';
GROUP BY a.customer_n
------------------------------------------------------------

*****************************
Combined SubQueries
*****************************

8)
Worse:
-------------------------------------------------------------
SELECT customer_n
FROM exdoc
WHERE total_doc =
(SELECT MAX( total_pos)
FROM expos)
AND doc_dd =
(SELECT MAX( doc_dd)
FROM expos);
-------------------------------------------------------------
Better:
-------------------------------------------------------------
SELECT customer_n
FROM exdoc
WHERE (total_doc, doc_dd) =
(SELECT MAX( total_pos)
, MAX( doc_dd)
FROM expos);
--------------------------------------------------------------

*********************************
Using EXISTS, IN, Join
*********************************

9-11)
Effectivity of these 3 Query variants depends from data inside the tables:
----------------------------------------------------------------------
SELECT customer_n
FROM exdoc a
WHERE EXISTS
(SELECT 1
FROM expos b
WHERE b.doc_nd = a.doc_nd
AND b.good_n = 'Igor');
-----------------------------------------------------------------------
SELECT customer_n
FROM exdoc
WHERE doc_nd IN
(SELECT doc_nd
FROM expos
WHERE good_n = 'Igor');
------------------------------------------------------------------------
SELECT a.customer_n
FROM exdoc a
, expos b
WHERE b.doc_nd = a.doc_nd
AND b.good_n = 'Igor';
------------------------------------------------------------------------

*********************
Using DISTINCT
*********************

12)
Worse:
-------------------------------------------------------------------------
SELECT DISTINCT a.doc_nd
, a.customer_n
FROM exdoc a
, expos b
WHERE a.doc_nd = b.doc_nd
AND b.good_n = 'Igor';
--------------------------------------------------------------------------
Better:
--------------------------------------------------------------------------
SELECT a.doc_nd
, a.customer_n
FROM exdoc a
WHERE EXISTS
(SELECT 1
FROM expos b
WHERE b.doc_nd = a.doc_nd
AND b.good_n = 'Igor');
----------------------------------------------------------------------------

************************
Using DECODE
************************

Decrease the quantity of looking the same tables by using DECODE

13) -------------------------------------------------------------------------
SELECT COUNT(*)
FROM exdoc
WHERE doc_nd = '139'
AND customer_n = 'Lukyanov';

...
SELECT COUNT(*)
FROM exdoc
WHERE doc_nd = '152'
AND customer_n = 'Lukyanov';
14)---------------------------------------------------------------------------
SELECT COUNT( DECODE( doc_nd, 139, 'X', NULL )) cnt_139
, COUNT( DECODE( doc_nd, 152, 'X', NULL )) cnt_152
FROM exdoc
WHERE customer_n = 'Lukyanov';
-------------------------------------------------------------------------------

*************************
Using UNION ALL
*************************

15)
Worse:
--------------------------------------------------------------------------------
SELECT doc_nd, sum_tax
FROM expos
WHERE doc_dd = '12-sep-12'
UNION
SELECT doc_nd, sum_tax
FROM exsvc
WHERE doc_dd = '12-sep-12';
--------------------------------------------------------------------------------
Better:
--------------------------------------------------------------------------------
SELECT doc_nd, sum_tax
FROM expos
WHERE doc_dd = '12-sep-2012'
UNION ALL
SELECT doc_nd, sum_tax
FROM exsvc
WHERE doc_dd = '12-sep-2012';
---------------------------------------------------------------------------------

************************
Using Anti-Join
************************

16)------------------------------------------------------------------------------
SELECT *
FROM exdoc
WHERE doc_nd NOT IN
(SELECT doc_nd
FROM expos
WHERE good_n = 'Igor');
17)--------------------------------------------------------------------------------
SELECT *
FROM exdoc a
WHERE NOT EXISTS
(SELECT NULL
FROM expos b
WHERE a.doc_nd = b.doc_nd
AND b.good_n = 'Igor');
18)---------------------------------------------------------------------------------
SELECT a.*
FROM exdoc a
, expos b
WHERE b.doc_nd (+) = a.doc_nd
AND b.good_n (+) = 'Igor'
AND b.ROWID IS NULL;
Page 1 of 1 All times are UTC + 2 hours [ DST ]
Powered by phpBB® Forum Software © phpBB Group
http://www.phpbb.com/