In this topic i will presents to u some of the techniques to make ur query better than before, some u will find it easy & u know it & some r not.

1- Always Don't forget table joins to avoid Cross Product Desaster.

2- Use Fully Qualified Column References When Performing Joins:
That way, the database doesn’t have to search for each column in the tables used in your query.
(ex. SELECT p.name, pt.name, p.description, p.price
FROM products p, product_types pt
WHERE p.product_type_id = pt.product_type_id
)

3- Add Indexes to Tables:
A good rule of thumb is that an index is useful when you expect a single query to retrieve 10 percent or less of the total rows in a table.

4- Use WHERE Rather than HAVING:
(ex.
-- BAD (uses HAVING rather than WHERE)
SELECT product_type_id, AVG(price)
FROM products
GROUP BY product_type_id
HAVING product_type_id IN (1, 2);

-- GOOD (uses WHERE rather than HAVING)
SELECT product_type_id, AVG(price)
FROM products
WHERE product_type_id IN (1, 2)
GROUP BY product_type_id;
)

5- Use UNION ALL Rather than UNION:
You use UNION ALL to get all the rows retrieved by two queries, including duplicate rows; you use UNION to get all non-duplicate rows retrieved by the queries. Because UNION removes duplicate rows (which takes some time to do), use UNION ALL whenever possible

6- Use EXISTS Rather than IN:
Use IN to check if a value is contained in a list. EXISTS is different from IN: EXISTS just checks for the existence of rows, whereas IN checks actual values. EXISTS typically offers better performance than IN with subqueries.
(ex.
-- BAD (uses IN rather than EXISTS)
SELECT product_id, name
FROM products
WHERE product_id IN
(SELECT product_id
FROM purchases);

-- GOOD (uses EXISTS rather than IN)
SELECT product_id, name
FROM products outer
WHERE EXISTS
(SELECT 1
FROM purchases inner
WHERE inner.product_id = outer.product_id);
)

7- Use EXISTS Rather than DISTINCT:
You can suppress the display of duplicate rows using DISTINCT; you use EXISTS to check for the existence of rows returned by a subquery. Whenever possible, you should use EXISTS rather than DISTINCT because DISTINCT sorts the retrieved rows before suppressing the duplicate rows.

(ex.
-- BAD (uses DISTINCT when EXISTS would work)
SELECT DISTINCT pr.product_id, pr.name
FROM products pr, purchases pu
WHERE pr.product_id = pu.product_id;

-- GOOD (uses EXISTS rather than DISTINCT)
SELECT product_id, name
FROM products outer
WHERE EXISTS
(SELECT 1
FROM purchases inner
WHERE inner.product_id = outer.product_id);

)



I hope that i give u something useful
(By the way i read it from a book & paste it here to save u the time of searching)

الله يعطيك العافية وفــــــــــــــــــــــــــــــــــي إنتظار المزيد من المشاركات و ألف شكر ...
مع تحياتي