O'Reilly logo

Sams Teach Yourself MySQL by Chris Newman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Self-Joins

Sometimes you want to join a table to itself. You can do this either by putting the same table name in the FROM clause twice or by using that table after a JOIN keyword.

The following example uses a self-join to find people in the customer_contacts table who belong to the same company as a named person. You achieved the same result in Lesson 10, “Using Subqueries,” using a subquery—this is a different approach to the same problem.

mysql>  SELECT c2.first_name, c2.last_name
    ->  FROM customer_contacts c1
    ->  JOIN customer_contacts c2
    ->  ON c1.customer_code = c2.customer_code
    ->  WHERE c1.first_name = 'Benjamin'
    ->  AND c1.last_name = 'Franklin'; +------------+-----------+ | first_name | last_name | +------------+-----------+ | Albert | ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required