Joining a Table to Itself: The Self Join

Today's final topic is the technique of joining a table to itself, also described as performing a self join. The syntax of this operation is similar to joining two tables. For example, to join table TABLE1 to itself, type this:

						SELECT *
						FROM TABLE1, TABLE1

ROW        REMARKS    ROW        REMARKS
========== ========== ========== ========

row 1      Table 1    row 1      Table 1
row 1      Table 1    row 2      Table 1
row 1      Table 1    row 3      Table 1
row 1      Table 1    row 4      Table 1
row 1      Table 1    row 5      Table 1
row 1      Table 1    row 6      Table 1
row 2      Table 1    row 1      Table 1
row 2      Table 1    row 2      Table 1
row 2      Table 1    row 3      Table 1
row 2      Table 1    row 4      Table 1
row 2      Table 1    row 5      Table 1
row 2      Table 1    row 6      Table 1
row 3 Table 1 row 1 Table 1 row 3 Table 1 row 2 Table ...

Get Sams Teach Yourself SQL in 21 Days, Fourth Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial