SQL Child / Lining Tables Up

What It Means To Line Two Tables Up

Combining two collections of records sounds mechanical, but the mental picture you hold determines whether you get it right.

Picture two lists on a desk. One holds customers, one record each. The other holds orders, and every order carries the identifier of the customer who placed it. You want a single list showing each order with the customer's name attached. So you line the two lists up on that shared identifier: for every order, find the customer record carrying the same identifier, and set them side by side. That is the whole operation. Nothing more magical is happening. You are matching records from one collection to records in another on a value they hold in common.

The important thing is what the resulting list is a list of. It is not a list of customers any more, and it is not really a list of orders either, although in this particular case it happens to have one entry for each order. It is a list of matched pairs. That distinction sounds pedantic until the day the matching is not one to one, and then it becomes the difference between a right answer and a badly wrong one. Always be able to say, out loud, what a single line in your combined result represents.

So before you combine anything, ask two questions about the shared value. In the first collection, can the same value appear more than once? In the second? If the answer is no and no, you will get one pair for each match and life is simple. If the answer is yes anywhere, you are about to produce more lines than you started with, and you need to have decided in advance whether that is what you want. Asking these two questions takes ten seconds and prevents the most common error in the whole field.