When we write a JOIN statement in SQL, it usually goes like this<\/p>\n
Does it ever make sense for the If we have a parent → child relationship, and we want to left-join parents to children with specific criteria, this criteria should NOTE: this is NOT<\/b> the same as putting the condition in the Code on Github: https:\/\/github.com\/ikriv-samples\/join<\/a><\/p>\n To create the database <\/p>\n This will create a simple database with two tables: brands and promotions.<\/p>\n We need to fetch brands alongside upcoming promotions, i.e. promotions that start between 2024-12-05 and 2024-12-31. If a brand has no upcoming promotions, we still want to see it, but with NULLs in the promotion data columns, following the LEFT JOIN semantics.<\/p>\n The SQL query to do so is<\/p>\n Note how the promotion condition is included as part of the ON criteria. <\/p>\n If we want to see all promotions, we execute a traditional JOIN with id comparison:<\/p>\n Beware of applying the promotion criteria AFTER the join. This will select only brands with upcoming promotions and leave out all other brands.<\/p>\n Only the brands with upcoming promotions are retrieved, and all other brands are hidden.<\/p>\n With Many thanks to Arkadi Klepatch<\/a> for giving me the idea of this post.<\/p>\n","protected":false},"excerpt":{"rendered":"SELECT {columns} FROM parent LEFT JOIN child ON child.parent_id = parent.id<\/code><\/p>\nON<\/code> condition to be more elaborate? The answer is a definite YES<\/b> as demonstrated by this example.<\/p>\n
\ngo to the ON<\/code> condition of the join. For example, if we have brands with promotions, and we want to fetch brands alongside promotions that start soon, we should put “starts soon” into the ON condition like this:<\/p>\n\r\nSELECT b.name, p.description, p.start_date, p.end_date\r\nFROM brands b LEFT JOIN promotions p \r\nON b.id = p.brand_id AND p.start_date BETWEEN DATE'2024-12-05' AND DATE'2024-12-31'<\/font>\r\n<\/pre>\n
WHERE<\/code> clause.<\/p>\nCreating the Database<\/h2>\n
\n
git clone https:\/\/github.com\/ikriv-samples\/join.git<\/code><\/li>\n.\/run-container.sh<\/code>.
\nNOTE: you should have Docker installed on your system.<\/li>\n.\/sql.sh create_db.sql<\/code><\/li>\n<\/ol>\n\r\nmysql> select * from brands;\r\n+----+-------------------------+\r\n| id | name |\r\n+----+-------------------------+\r\n| 1 | ACME |\r\n| 2 | Road Runner Auto Repair |\r\n| 3 | Bugs Bunny Foods |\r\n| 4 | Daffy Duck Outfitters |\r\n+----+-------------------------+\r\n<\/pre>\n
\r\nmysql> select * from promotions;\r\n+----+----------+---------------------------+------------+------------+\r\n| id | brand_id | description | start_date | end_date |\r\n+----+----------+---------------------------+------------+------------+\r\n| 1 | 1 | 50% off all items | 2024-12-01 | 2024-12-31 |\r\n| 2 | 1 | Buy one bomb get one free | 2024-12-05 | 2024-12-25 |\r\n| 3 | 3 | 50 carrots for $3 | 2024-10-05 | 2024-10-07 |\r\n| 4 | 3 | 100 carrots for $5 | 2024-12-10 | 2024-12-15 |\r\n+----+----------+---------------------------+------------+------------+\r\n<\/pre>\n
Fetching brands alongside upcoming promotions<\/h2>\n
\r\nSELECT b.name, p.description, p.start_date, p.end_date\r\nFROM brands b\r\nLEFT JOIN promotions p \r\nON b.id = p.brand_id AND p.start_date BETWEEN DATE'2024-12-05' AND DATE'2024-12-31'\r\n<\/pre>\n
.\/sql.sh upcoming_promotions.sql<\/code><\/p>\n\r\n+-------------------------+---------------------------+------------+------------+\r\n| name | description | start_date | end_date |\r\n+-------------------------+---------------------------+------------+------------+\r\n| ACME | Buy one bomb get one free | 2024-12-05 | 2024-12-25 |\r\n| Road Runner Auto Repair | NULL | NULL | NULL |\r\n| Bugs Bunny Foods | 100 carrots for $5 | 2024-12-10 | 2024-12-15 |\r\n| Daffy Duck Outfitters | NULL | NULL | NULL |\r\n+-------------------------+---------------------------+------------+------------+\r\n<\/pre>\n
Compare to other usages of JOIN<\/h2>\n
\r\nSELECT b.name, p.description, p.start_date, p.end_date\r\nFROM brands b\r\nLEFT JOIN promotions p\r\nON b.id = p.brand_id\r\n<\/pre>\n
.\/sql.sh all_promotions.sql<\/code><\/p>\n\r\n+-------------------------+---------------------------+------------+------------+\r\n| name | description | start_date | end_date |\r\n+-------------------------+---------------------------+------------+------------+\r\n| ACME | Buy one bomb get one free | 2024-12-05 | 2024-12-25 |\r\n| ACME | 50% off all items | 2024-12-01 | 2024-12-31 |\r\n| Road Runner Auto Repair | NULL | NULL | NULL |\r\n| Bugs Bunny Foods | 100 carrots for $5 | 2024-12-10 | 2024-12-15 |\r\n| Bugs Bunny Foods | 50 carrots for $3 | 2024-10-05 | 2024-10-07 |\r\n| Daffy Duck Outfitters | NULL | NULL | NULL |\r\n+-------------------------+---------------------------+------------+------------+\r\n<\/pre>\n
\r\nFROM brands b\r\nLEFT JOIN promotions\r\nON b.id = p.brand_id\r\nWHERE p.start_date BETWEEN DATE'2024-12-05' AND DATE'2024-12-31' -- WRONG!\r\n<\/pre>\n
.\/sql.sh wrong_promotions.sql<\/code><\/p>\n\r\n+------------------+---------------------------+------------+------------+\r\n| name | description | start_date | end_date |\r\n+------------------+---------------------------+------------+------------+\r\n| ACME | Buy one bomb get one free | 2024-12-05 | 2024-12-25 |\r\n| Bugs Bunny Foods | 100 carrots for $5 | 2024-12-10 | 2024-12-15 |\r\n+------------------+---------------------------+------------+------------+\r\n<\/pre>\n
Conclusion<\/h2>\n
LEFT JOIN<\/code>, including a criteria in the ON<\/code> clause has different effect from including the same criteria in the WHERE<\/code> clause. WHERE<\/code> clause filters out the parent records that don’t have any children that match the criteria, effectively turning LEFT JOIN<\/code> into an INNER JOIN<\/code>. To maintain the LEFT JOIN<\/code> semantics, any criteria pertaining to the child table should be placed in the ON<\/code> clause.<\/p>\n