Subqueries and Outside Reference with Correlated Subqueries
I am updating my SQL.
I read about subqueries and the ability to reference outside with correlated subqueries.
Example:
SELECT *
FROM ORDERS O
WHERE 'ROAD BIKE' =
(SELECT DESCRIPTION FROM PART P WHERE P.PARTNUM = O.PARTNUM)
This is equivalent to joining:
SELECT O.ORDEREDON, O.NAME,
O.PARTNUM, O.QUANTITY, O.REMARKS
FROM ORDERS O, PART P
WHERE P.PARTNUM = O.PARTNUM AND P.DESCRIPTION = 'ROAD BIKE'
My problem is I didn't get the first form and when / why do we use it. When are external links useful?
source to share
Orders have a part number reference, so the Orders table has a foreign key for part numbers.
We want all Orders where the part number is for "Road Bike".
The first form first runs a subquery on each record to check if O.PARTNUM is the part number for "Road Bike".
Think about it, the main query goes through every record in the Orders table. In each record, it performs an additional query, where the query uses the PARTNUM field. So, if you are using the Orders PARTNUM record in the subquery, select to find the record in the PART table with this PARTNUM and select the field DESCRIPTION. The where clause of the main query then tests if "road bike" is equal to the DESCRIPTION returned from the subquery.
I would recommend not using the first form as it is a correlated query and you should avoid correlated queries for performance reasons, so use the second form. Better version of the first form:
SELECT *
FROM ORDERS O
WHERE O.PARTNUM =
(SELECT P.PARTNUM FROM PART P WHERE DESCRIPTION = 'ROAD BIKE')
This is not a correlated query. The database can execute the subquery once, get the PARTNUM for the record with "ROAD BIKE" as DESCRIPTION, and then run the main query with the WHERE O.PARTNUM clause equal to the result of the subquery.
source to share
In short, you should avoid correlated subqueries like the plague.
Correlation subqueries execute the inner query once for each row in the outer table. This leads to terrible performance (an outer table of 1 million rows will result in the inner query being executed 1 million times!)
Joining, on the other hand, is quite efficient and databases optimize them very well.
If possible, always express your query as an attachment in preference to a correlated subquery.
source to share