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

Structure of Retrieval Queries