Inner / Outer Join

From WikiName
Jump to: navigation, search

Table Definitions (using MySQL)

CREATE TABLE IF NOT EXISTS CUSTOMER
(
   UNIQUE_ID integer unsigned PRIMARY KEY AUTO_INCREMENT
   , FIRST_NAME VARCHAR(60)   
   , LAST_NAME VARCHAR(60)
   , ADDRESS_1 VARCHAR(120)
   , ADDRESS_2 VARCHAR(120)
   , ZIP_CODE INTEGER unsigned
);
CREATE TABLE IF NOT EXISTS CITIES
(
   ZIP_CODE integer unsigned PRIMARY KEY 
   , CITY VARCHAR(255)   
   , STATE VARCHAR(2)   
);

Inserted rows

INSERT INTO CUSTOMER VALUES ( 1, "Fred", "Flintstone", "1234 Bedrock Blvd", "", 00001);
INSERT INTO CUSTOMER VALUES ( 1, "George", "Jetson", "1 Outer World Blvd", "", null);
INSERT INTO CITIES VALUES ( 00001, "Bedrock", "SA" );
INSERT INTO CITIES VALUES ( 95070, "Saratoga", "CA" );
INSERT INTO CITIES VALUES ( 95014, "Cupertino", "CA" );
INSERT INTO CITIES VALUES ( 95124, "San Jose", "CA" );

Inner Join

select Customer.First_Name, Cities.City from Customer inner join Cities ON Cities.zip_code = Customer.zip_code;

Result set

+------------+---------+
| First_Name | City    |
+------------+---------+
| Fred       | Bedrock |
+------------+---------+

Outer (Left) join

select Customer.First_Name, Cities.City from Cities left outer join Customer ON Customer.zip_code = Cities.zip_code;

Result set

+------------+---------------+
| First_Name | City          |
+------------+---------------+
| Fred       | Bedrock       |
| NULL       | Cupertino     |
| NULL       | Saratoga      |
| NULL       | San Jose      |
+------------+---------------+

Outer (Right) join

select Customer.First_Name, Cities.City from Cities right outer join Customer ON Customer.zip_code = Cities.zip_code;

Result set

+------------+---------+
| First_Name | City    |
+------------+---------+
| Fred       | Bedrock |
| George     | NULL    |
+------------+---------+


Outer Full join

  • This does not work on MySQL:
select Customer.First_Name, Cities.City from Cities full outer join Customer ON Customer.zip_code = Cities.zip_code;
  • Had to use this workaround instead:
select Customer.First_Name, Cities.City from Cities left outer join Customer ON Customer.zip_code = Cities.zip_code
union 
select Customer.First_Name, Cities.City from Cities right outer join Customer ON Customer.zip_code = Cities.zip_code;

Result set

+------------+---------------+
| First_Name | City          |
+------------+---------------+
| Fred       | Bedrock       |
| NULL       | Cupertino     |
| NULL       | Saratoga      |
| NULL       | San Jose      |
| George     | NULL          |
+------------+---------------+