Trying to eliminate nested queries

carlosn · #1 (**permalink**) 03-15-11, 12:43

I have two tables. The first is a table of vehicles. The second is a table of events that have occurred to those vehicles (purchased, sold, accident, destroyed, etc). The events table has columns for the event date and the type of event. Obviously one vehicle may have multiple events associated with it.

I've created a query that returns the vehicle with its purchased and sold dates (assumes that only one of each event exist per vehicle, which is a good assumption in my case, but I don't have logic to restrict it). The query involves two nested queries which are essentially the same, except for the WHERE clause. I'm wondering if there is a way to simplify it. While the example below is pretty simple, in reality the nested queries are a bit more complex that I've shown -- but they are indeed identical, except for the WHERE clause. In addition, the actual query will likely have two or three additional columns, all from other similar nested queries.

Code:

SELECT	Vehicle.Name, BuyDate.EventDate AS [Purchased], SellDate.EventDate AS [Sold]
FROM	Vehicle 
LEFT OUTER JOIN
	(SELECT Event.Vehicle_ID, Event.EventDate
	FROM    Event INNER JOIN
	WHERE  	(EventType = 'Purchase')) AS BuyDate 
ON Vehicle.Vehicle_ID = Event.Vehicle_ID 
LEFT OUTER JOIN
	(SELECT Event.Vehicle_ID, Event.EventDate
	FROM    Event INNER JOIN
	WHERE  	(EventType = 'Sale')) AS SellDate 
ON Vehicle.Vehicle_ID = Event.Vehicle_ID

shammat · #2 (**permalink**) 03-15-11, 13:17

Your example is invalid because of this part:

Code:

SELECT Event.Vehicle_ID, Event.EventDate
FROM    Event INNER JOIN
WHERE  	(EventType = 'Purchase')

You have two errors in your SQL

- You are missing a table after the INNER JOIN or the INNER JOIN is simply not necessary.
- this is not ANSI SQL because of the illegal column alias: [Sold] (illegal because of the brackets. ANSI SQL requires double quotes for object names)

having said this, you don't need sub-selects here:

Code:

SELECT  Vehicle.Name, 
        BuyDate.EventDate AS Purchased, 
        SellDate.EventDate AS Sold
FROM Vehicle 
LEFT OUTER JOIN event AS BuyDate 
   ON Vehicle.Vehicle_ID = Event.Vehicle_ID AND event.eventType = 'Purchase'
LEFT OUTER JOIN event as SellDate 
   ON Vehicle.Vehicle_ID = Event.Vehicle_ID AND event.eventType = 'Sale'

carlosn · #3 (**permalink**) 03-15-11, 13:36

Agh. I oversimplified my example, to the point that as shammat stated, I no longer need the nested queries. In my original, each of the nested queries has some joins in them (That's the reason for the dangling INNER JOIN in the original example, which is obviously incorrect).

A less simplified variant (hopefully with no more errors):

Code:

SELECT	Vehicle.Name, 
		BuyDate.EventDate AS Purchased, 
		SellDate.EventDate AS Sold
FROM Vehicle 
LEFT OUTER JOIN
	(SELECT Event.Vehicle_ID, Event.EventDate
	FROM    Event 
	INNER JOIN EventTypes
	ON Event.EventType_ID = EventTypes.EventType_ID
	WHERE  	(EventType = 'Purchase')) AS BuyDate 
ON Vehicle.Vehicle_ID = Event.Vehicle_ID 
LEFT OUTER JOIN
	(SELECT Event.Vehicle_ID, Event.EventDate
	FROM    Event 
	INNER JOIN EventTypes
	ON Event.EventType_ID = EventTypes.EventType_ID
	WHERE  	(EventType = 'Sale')) AS SellDate 
ON Vehicle.Vehicle_ID = Event.Vehicle_ID

shammat · #4 (**permalink**) 03-15-11, 13:42

The only thing I can think of to make this simpler is to use a CTE:

Code:

WITH evt AS (
    SELECT Event.Vehicle_ID, Event.EventDate, EventTypes.EventType
    FROM Event 
      INNER JOIN EventTypes
         ON Event.EventType_ID = EventTypes.EventType_ID
) 
SELECT	Vehicle.Name, 
        BuyDate.EventDate AS Purchased, 
        SellDate.EventDate AS Sold
FROM Vehicle 
LEFT OUTER JOIN evt AS buyDate 
   ON buyDate.Vehicle_Id = vehicle.vehicle_ID AND evt.EventType = 'Purchase'
LEFT OUTER JOIN evt AS sellDate 
   ON sellDate.Vehicle_Id = vehicle.vehicle_ID AND evt.EventType = 'Sale'

carlosn · #5 (**permalink**) 03-15-11, 14:04

Thanks, that is perfect. I didn't know about CTEs - it is exactly what I needed in this scenario. While in the example query it does not seem to simplify things much, in my real query it makes it much more readable. (And easier to document!)

shammat · #6 (**permalink**) 03-15-11, 14:11

Beware of performance pitfalls. A CTE can make things faster or slower.

If the CTE reduces a large result set to just a few rows, then it makes things probably faster (by re-using the small result).

If the result of the CTE is so large that it doesn't fit into memory, it will spill to disk and thus will make things slower (mainly because nothing will be indexed in that result)

JarlH · #7 (**permalink**) 03-16-11, 04:17

Quote:

I've created a query that returns the vehicle with its purchased and sold dates (assumes that only one of each event exist per vehicle, which is a good assumption in my case, but I don't have logic to restrict it).

Does this mean you want exactly one purchased date and exactly one sold date for each vehicle? (No matter which dates, if several exists?)

If that's what you want, I'd use correlated subqueries:

SELECT Vehicle.Name,
(SELECT MAX(EventDate) FROM evt
WHERE Vehicle_Id = V.vehicle_ID AND EventType = 'Purchase') AS Purchased,
(SELECT MAX(EventDate) FROM evt
WHERE Vehicle_Id = V.vehicle_ID AND EventType = 'Sold') AS Sold
FROM Vehicle as V

The MAX aggregate is there just in case several sold/purchased dates exist for a vehicle.

Internet Services

Web Development

Digital Marketing

Consumer Tech