"outer join": mismatches (rows in one table not matched by any row in the other table) are included too. superset of inner join. SELECT ... FROM t1 LEFT JOIN t2 ON fi=fj... fi is column in t1, fj is column in t2 SELECT ... FROM t1 LEFT JOIN t2 USING(fk)... fk is column in both tables left table t1 is reference table; output is produced for each of its rows, those with matching row(s) in the right table (the same as inner join) and those with no matching row(s) in the right table t2 and in which any selected columns of t2 will be displayed as NULL. SELECT p_code,p_descript,v_name FROM product LEFT JOIN vendor ON product.v_code=vendor.v_code; SELECT p_code,p_descript,v_name FROM product LEFT JOIN vendor USING(v_code); To find *only* the mismatches, use Where clause to restrict result to only those rows that have Nulls in right table's column(s): SELECT p_code,p_descript,v_name FROM product LEFT JOIN vendor USING(v_code) WHERE v_name IS NULL; (this is the complement of the inner join, ie. inner join + "mismatch join" = outer join) Parent rows that are not matched by any child: i.e. childless parents. SELECT parentFields FROM parent LEFT JOIN child ON pk=fk WHERE anyChildField IS NULL; (parent parent child child) #vendors (parents) that have no products (childs) SELECT v_code,v_name FROM vendor LEFT JOIN product USING(v_code) WHERE p_descript IS NULL; #all the child fields will be Null can be done with subquery instead of join: SELECT v_code,v_name FROM vendor WHERE v_code NOT IN (SELECT v_code FROM product WHERE v_code IS NOT NULL); (nulls contaminate) #note that qualifying code not needed because only vendor table is used in outer select. ************** Find referential integrity problems of imported data: left table's foreign key does not match primary key of any row of right table. i.e. orphan child (or Null). SELECT childFields FROM child LEFT JOIN parent ON fk=pk WHERE anyParentField IS NULL; (child child parent parent) #product v_codes that are invalid (i.e. not in vendor table) or are Null SELECT p_code,p_descript FROM product LEFT JOIN vendor USING(v_code) WHERE v_name IS NULL; #To disclude Null children: SELECT p_code,p_descript FROM product LEFT JOIN vendor USING(v_code) WHERE v_name IS NULL AND v_code IS NOT NULL;