How do you query supertypes to get subtype data?

Question

I'm a bit of a DBA newbie here (coming from a programming background having to fill the role of a DBA), so please excuse by blantant and somewhat basic ignorance.

Using a simple relationship between a supertype VEHICLE and its relative subtypes that are actual vehicles, like cars, boats or planes etc. If you query the Vehicle table to get some results, say, show me all vehicles that have a colour = RED, then how does the database know how to query the CAR table, seeing as that is the only table in this example that has a column to do with colour.

I supsect I must have some kind of vehicle type lookup type table, which should also point to the table relevant for that subtype, but I'm not sure if I'm over complicating things or if this is even good practice. Any help by experienced, wiser heads would be much appreciated.

VEHICLE (supertype)

╔════════╦══════════╗
║ VEH_ID ║  FOO_COL ║
╠════════╬══════════╣
║     28 ║ foo      ║
║     52 ║ column   ║
║     18 ║ data     ║
╚════════╩══════════╝

CAR (subtype)

╔════════╦══════════╗
║ CAR_ID ║  COLOUR  ║
╠════════╬══════════╣
║    52  ║ red      ║
╚════════╩══════════╝

BOAT (subtype)

╔════════╦══════════╗
║ BOAT_ID║  SPEED   ║
╠════════╬══════════╣
║    18  ║ VERY FAST║
╚════════╩══════════╝

Answer 1

Unless you use dynamic SQL to build the query (which adds no value anyway) then SQL is a "fixed column" language. That is, you'd have to send all columns you need. This example is bad in that you have all columns but you get the idea.

SELECT
   V.*,
   C.*,
   B.*
FROM
   Vehicle V
   LEFT JOIN
   Car C ON V.VEH_ID = C.COLOUR
   LEFT JOIN
   Boat B ON V.VEH_ID = B.BOAT_ID

Another option is to hide the VEHICLE-CAR join behind a view "CARS" and VEHICLE-BOAT join behind another view "BOATS". However, you can't UNION them as the have different columns and types

Basically, you don't query supertypes/subtypes like this because each subtype gives a different resultset.