About 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