How can you write this query?

Question

I have two tables. The structure of tables is as follows.

TRAILERS_INVENTORY

• TRAILER_TYPE VARCHAR2(100)
• TRAILER_LENGTH INT
• TRAILER_WIDTH INT
• YEAR_OF_MANUFACTURE INT

NEW_INVENTORY

• NEW_INVENTORY_TYPE_ID INT,
• TRAILER_TYPE VARCHAR2(100),
• TRAILER_LENGTH INT,
• TRAILER_WIDTH INT,
• YEAR_OF_MANUFACTURE INT

I want to list all of the trailers that do not have a NEW_INVENTORY_TYPE_ID. I am using Oracle 9.2.

One approach is to use a set operation along the lines of

SELECT TRAILER_TYPE
 FROM TRAILERS_INVENTORY 
WHERE TRAILER_TYPE NOT IN (SELECT TRAILER_TYPE, 
                                  TRAILER_LENGTH, 
                                  TRAILER_WIDTH, 
                                  YEAR_OF_MANUFACTURE 
                             FROM TRAILERS_INVENTORY 
                           INTERSECT 
                           SELECT TRAILER_TYPE, 
                                  TRAILER_LENGTH, 
                                  TRAILER_WIDTH, 
                                  YEAR_OF_MANUFACTURE 
                             FROM NEW_INVENTORY);

IS THERE ANOTHER APPROACH AVAILABLE?

Answer 1

I see a couple of options

SELECT
    TRAILER_TYPE
FROM
    TRAILERS_INVENTORY
WHERE
    TRAILER_TYPE NOT IN (SELECT TRAILER_TYPE FROM NEW_INVENTORY)

Or:

SELECT
    TRAILER_TYPE
FROM
    TRAILERS_INVENTORY
LEFT JOIN
    NEW_INVENTORY
    ON (NEW_INVENTORY.TRAILER_TYPE = TRAILERS_INVENTORY.TRAILER_TYPE)
WHERE
    NEW_INVENTORY.NEW_INVENTORY_TYPE_ID IS NULL

Answer 2

What about:

SELECT 
TRAILER_TYPE, 
TRAILER_LENGTH, 
TRAILER_WIDTH, 
YEAR_OF_MANUFACTURE 
FROM TRAILERS_INVENTORY 
MINUS
SELECT 
TRAILER_TYPE, 
TRAILER_LENGTH, 
TRAILER_WIDTH, 
YEAR_OF_MANUFACTURE 
FROM NEW_INVENTORY

Answer 3

So, the question is how can you perform this query without using a "set" operation?

Can I ask why you wouldn't use a set?

Everything you do in SQL is based on sets, but if you want a slower, less elegant solution you can reduce your set size to 1 and loop through TRAILERS_INVENTORY using PL/SQL.

declare
    v_exists varchar2(1 char);
begin
    for item in (select distinct trailer_type from trailers_inventory) loop
        begin
            select 'Y' into v_exists from new_inventory
            where trailer_type = item.trailer_type;
        exception
            when no_data_found then
                v_exists := null;
        end;
        if v_exists is null then
            dbms_output.put_line(item.trailer_type || ' not in ' || ' NEW_INVENTORY');
        end if;
    end loop;
end;
/

Eek! That's why set operations are the way to go.

:)