Join Comparison Operators

The JOIN operator matches rows by comparing values in one table with values in another. You can decide exactly what constitutes a match. Your choices fall into two broad categories:

• Match on Equality   Typically, you match rows when the respective column values are equal. For example, to create a result set in which each row contains a full description of each publisher (that is, with columns from the publishers table and the pub_info table), you use a join matching rows where the values of pub_id in the respective tables are equal. The resulting SQL might look like this:

  SELECT *
      FROM publishers INNER JOIN pub_info 
        ON publishers.pub_id 
        =  pub_info.pub_id
  

• Other   You can match rows using some test other than equality. For example, to find the employees and the jobs for which they are underqualified, you can join employee with jobs, matching rows in which the job's minimum required level exceeds the employee's job level. The resulting SQL might look like this:

  SELECT fname, minit, lname, job_desc, job_lvl, min_lvl

  FROM employee INNER JOIN jobs

  ON employee.job_lvl

  < jobs.min_lvl

For more information on comparison operators, see Comparison Operators.

See Also

Reference

Join Properties

Other Resources

Querying with Joins

Designing Queries and Views