[Solved] SQL SELECT looking for a name like a substring in a join

Question

I would read the “not certified on any” to be a check for the existence of a row.

If a matching row exists, then don’t return the employee. Only return the emplouee if a matching row doesn’t exist.

How would you find a matching row, to find out if an employee is “certified on any”?

There are several approaches. The two best approaches to use 1) anti-join and 2) NOT EXISTS (correlated subquery).

example NOT EXISTS (correlated subquery)

Of the two approaches this one is easier to see how it works.

  FROM e
 WHERE NOT EXISTS ( SELECT 1
                      FROM certified c
                      JOIN aircraft a 
                        ON a.id = c.a_id
                     WHERE a.aname LIKE '%b%'   
                       AND c.e_id = e.id
                  )

Note the reference to the outer table (e.id) in the predicate of the subquery. The subquery is “correlated” with the outer query.

Think of if this way: for every row returned by the outer query, the subquery is executed, passing in the value of e.id. (The optimizer doesn’t have to perform the operation this way; that’s just an easy way of thinking about what we’re asking for.)

If the subquery returns 1 or more rows, the condition EXISTS is satisfied, and returns TRUE. If the subquery returns zero rows, EXISTS evaluates to FALSE.

example of anti-join pattern

This approach can take a bit to get your brain wrapped around. Once you do “get it”, it’s an invaluable tool to keep handy in the SQL toolbelt.

If we use an OUTER JOIN, and pull back all rows from e along with any matching rows, then we can “exclude” the rows that found a match.

  FROM e
  LEFT
  JOIN ( SELECT c.e_id
           FROM certified c
           JOIN aircraft a
             ON a.id = c.a_id
          WHERE a.aname LIKE '%b%'
         GROUP BY c.e_id
       ) b
    ON b.e_id = e.id
 WHERE b.e_id IS NULL

The inline view query is materialized into a derived table named b. That query is intended to return the id of every employee that is certified to fly any aircraft meeting the specified criteria. Then the rows in the derived table are outer joined to e.

The “trick” is the outer join (to include both rows with matches, and rows without matches, and the condition in the WHERE clause that excludes rows that had matches.

I expect someone else will provide an example of how to use a NOT IN (subquery). With that approach, beware of what happens if the subquery returns any NULL values. (HINT: you will want to ensure that the subquery will never ever return a NULL.)

This demonstrates only two of several possible approaches to satisfying the “is not certified on any” criteria.

Obviously, additional joins/subqueries will need to be added to evaluate the other criteria in the query.

Accepted Answer