SQL query to show the difference from the same table

Question

My application has a table containing snapshot inventory data from each year. For example, there is a vehicle inventory table with typical columns vehicle_id, vehicle_plate_num, vehicle_year, vehicle_make, etc., as well as a year indicating that the vehicle belongs.

Querying the entire table may result in the following:

Id  Plate Num   Year Make     Model    Color   Year Owned
---------------------------------------------------------
1   AAA555      2008 Toyota   Camry    blue    2009
2   BBB666      2007 Honda    Accord   black   2009
3   CCC777      1995 Nissan   Altima   white   2009
4   AAA555      2008 Toyota   Camry    blue    2010
5   BBB666      2007 Honda    Accord   black   2010
6   DDD888      2010 Ford     Explorer white   2010

(For better or worse, this table already exists, and this is not an option to reverse engineer the table and is a topic for another question) What you see here year after year, most cars are still in inventory, but there is always a situation where old ones are disposed of and new vehicles purchased. In the 1995 example above, the Nissan Altima was in the 2009 inventory, but no longer in the 2010 inventory. In the inventory of 2010, a new 2010 Ford Explorer appeared.

How can I create an effective query that takes two years and shows only the difference. For example, if I switch to 2009, 2010, the query should return

3   CCC777  1995  Nissan  Altima      white   2009

If I switch to 2010, 2009, the request should return

6   DDD888   2010  Ford   Explorer   white   2010

Edit: I should have added a comment after the reply from Kyle B., but the text area for comments is not very user friendly:

, , .

, - :

select q.* from (
    select f.*
    from inventory f 
      left join inventory s
      on (f.plate_num = s.plate_num 
         and f.year_owned = :first-year
         and s.year_owned = :second-year)
    where s.plate_num is null
) q
where q.year_owned = :second_year

Answer 1

, . , ( ) .

: :

select f.*
   from inventory f 
     left join inventory s
        on (f.plate_num = s.plate_num
           and s.year_owned = :second-year)
where s.plate_num is null 
  and f.year_owned = :first-year

, , , , , .

. . . postgresql.

Answer 2

select a.id, a.platenum, a.year, a.make, a.model, a.color, b.yearowned
from inventory a
join inventory b on a.platenum=b.platenum
where a.yearowned=___ and b.yearowned=___;

: oops, . ?

Answer 3

2010 , .

select * 
from cars
where Year_Owned = 2010
  and plate not in (
        select plate 
        from cars 
        where year_owned < 2010);

, , , , 2010 .

Answer 4

, "" ; , , SQL EXCEPT. , , , :

select plate, name from inventory where year_owned=2009
except
select plate, name from inventory where year_owned=2010

Answer 5

, . 90% .

:

, 2010 , 2009 :

select q.* from (
    select f.* from inventory f 
        left join inventory s
        on (f.plate_num = s.plate_num 
            and f.year_owned = 2010
            and s.year_owned = 2009)
        where s.plate_num is null
    ) q
where q.year_owned = 2010

---

, 2009 , 2010 :

select q.* from (
    select f.* from inventory f 
        left join inventory s
        on (f.plate_num = s.plate_num 
            and f.year_owned = 2009
            and s.year_owned = 2010)
        where s.plate_num is null
    ) q
where q.year_owned = 2009

• Pay attention to the subquery
• Works pretty fast for 100,000 records.