Home SQL query that returns records with certain NULL values when WHERE does not match
 I'm running the following query in Postgres: SELECT raw_times.*, efforts.id as effort_id, efforts.event_id as event_id, splits.id as split_id FROM raw_times INNER JOIN event_groups ON event_groups.id = raw_times.event_group_id INNER JOIN events ON events.event_group_id = event_groups.id INNER JOIN efforts ON efforts.event_id = events.id INNER JOIN aid_stations ON aid_stations.event_id = events.id INNER JOIN splits ON splits.id = aid_stations.split_id WHERE efforts.bib_number::text = raw_times.bib_number AND splits.parameterized_base_name = raw_times.parameterized_split_name  The idea is to locate matching bib numbers and split names and to return raw_time records with various relation ids populated. In plain English, the logic works like this: For each raw_time, check the event_group_id. An event_group has many events, and an event has many efforts, and the efforts table has a bib_number column. Bib number is unique within an event_group, but is not unique within the entire efforts table. So for each raw_time, because we know the event_group_id and the bib_number, we can determine which effort it relates to. Knowing the effort allows us also to know the event (because an effort has an event_id). An event has many splits through the aid_stations join table. Split name is unique within an event. Because we know the event (determined as described above) and we know the split name (it is a column on the raw_times table) we can determine the split_id. The query works as expected for records where there is a matching bib number and split name. But for records where either the bib number or split name does not match, the WHERE clause is not satisfied, so the raw_time record is not returned at all. I've tried the query with LEFT JOIN in place of each INNER JOIN, but I get the same result. What I'd like is for all raw_time records to be returned, but if there is no matching split name, return the record with NULL for the split_id, and if there is no matching bib number, return the record with NULL for effort_id, event_id, and split_id. The raw_times table looks like this: id event_group_id parameterized_split_name bib_number 3 53 finish 11 4 53 finish 603 5 53 finish 9999 6 53 nonexistent 603  The event_groups table looks like this: id 53 51  The events table looks like this: id event_group_id 26 53 28 53 18 51  The efforts table looks like this: id event_id bib_number 22183 26 11 22400 28 603 5747 18 11  The aid_stations table looks like this: id event_id split_id 236 26 30 237 26 31 238 26 106 239 26 111 240 26 112 241 26 109 242 26 113 254 28 119 255 28 118 138 18 1 150 18 16  The splits table looks like this: id parameterized_base_name 30 finish 31 start 106 aid-1 109 aid-4 111 aid-2 112 aid-3 113 aid-5 118 start 119 finish 1 start 16 finish  The query should return this: id event_group_id parameterized_split_name bib_number effort_id event_id split_id 3 53 finish 11 22183 26 30 4 53 finish 603 22400 28 119 6 53 nonexistent 603 22400 28 NULL 5 53 finish 9999 NULL NULL NULL  Here's a link to the ERD: https://github.com/SplitTime/OpenSplitTime/blob/master/erd.pdf