Querying and SQL Functions
Joining two tables
Join two tables on their common attribute.
If you list two tables in FROM, SQL forms their Cartesian product: every row paired with every row. Most of these pairs are meaningless.
To keep only the matching pairs, add a condition in WHERE, such as WHERE D.Name = M.Name. Here D and M are table aliases, short names given to the tables in that query. Once a table has an alias, use the alias (not the original name) in that query.
JOIN ... ON combines rows of two tables on a condition, usually that the primary key of one table equals the foreign key in the other. For example: SELECT ... FROM STUDENT JOIN MARKS ON STUDENT.RollNo = MARKS.RollNo.
NATURAL JOIN joins two tables on their common attribute automatically, and shows that column only once in the result.
To combine N tables, you need N − 1 joins: for example, 3 joins for 4 tables. Join on the linking key.
Practise this lesson
Questions that get harder as you improve, a Tutor that helps without giving the answer away, and progress you can see. Free for every learner.
No account, no email address. Opens the real product.