SELECT Statement “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization” ). However, the precedence of the comma operator is less than that of  INNER JOIN ,  CROSS JOIN ,  LEFT JOIN , and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form  Unknown column ’ col_name ’ in ’on clause’  may occur. Information about dealing with this problem is given later in this section. This example finds all rows in  left_tbl  with an  id  value that is not present in  right_tbl  (that is, all rows in  left_tbl  with no corresponding row in  right_tbl ). See Section 8.2.1.9, “Outer Join Optimization” . You can use other types of joins within  { OJ ... } , such as  INNER JOIN  or  RIGHT OUTER JOIN . This helps with compatibility with some third-party applications, but is not official ODBC syntax. Some join examples: • INNER JOIN  and  ,  (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table). •The  search_condition  used with  ON  is any conditional expression of the form that can be used in a  WHERE  clause. Generally, the  ON  clause serves for conditions that specify how to join tables, and the WHERE  clause restricts which rows to include in the result set. •If there is no matching row for the right table in the  ON  or  USING  part in a  LEFT JOIN , a row with all columns set to  NULL  is used for the right table. You can use this fact to find rows in a table that have no counterpart in another table: •The  USING( join_column_list )  clause names a list of columns that must exist in both tables. If tables  a  and  b  both contain columns  c1 ,  c2 , and  c3 , the following join compares corresponding columns from the two tables: •The  NATURAL [LEFT] JOIN  of two tables is defined to be semantically equivalent to an  INNER JOIN or a  LEFT JOIN  with a  USING  clause that names all columns that exist in both tables. • RIGHT JOIN  works analogously to  LEFT JOIN . To keep code portable across databases, it is recommended that you use  LEFT JOIN  instead of  RIGHT JOIN . • The  { OJ ... }  syntax shown in the join syntax description exists only for compatibility with ODBC. The curly braces in the syntax should be written literally; they are not metasyntax as used elsewhere in syntax descriptions. • STRAIGHT_JOIN  is similar to  JOIN , except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer processes the tables in a suboptimal order. SELECT * FROM table1, table2; SELECT left_tbl.* FROM { OJ left_tbl LEFT OUTER JOIN right_tbl ON left_tbl.id = right_tbl.id } WHERE right_tbl.id IS NULL; a LEFT JOIN b USING (c1, c2, c3) SELECT left_tbl.* FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id WHERE right_tbl.id IS NULL; 2673