This lesson uses the university database: the shape most database textbooks use. A student takes courses; the id column of takes holds the student's id.

USING

When the two join columns have the same name, USING is a short form of ON:

SELECT id, name, course_id
FROM student
JOIN takes USING (id);

• USING (id) means ON student.id = takes.id.
• The result has one id column, so id alone is not ambiguous.
• USING (a, b) joins on two columns.

NATURAL JOIN

NATURAL JOIN writes no condition. It joins on every column name that the two tables share. Textbooks use it often:

SELECT name, course_id
FROM student
NATURAL JOIN takes;

student and takes share only id, so this is the same join as USING (id).

Why NATURAL JOIN is risky

Add course to get each course's title:

SELECT name, title
FROM student
NATURAL JOIN takes
NATURAL JOIN course;

course shares course_id with takes. It also shares dept_name with student. The join keeps only the rows where the student's department is the same as the course's department. Leo Martins takes MA-110, a Math course, so that row is gone. No error tells you.

In the market tables, products NATURAL JOIN categories joins on id and name. It returns no rows.