Search code examples
sql-serveransi-sql

Just curious about SQL Joins

I'm just curious here. If I have two tables, let's say Clients and Orders. Clients have a unique and primary key ID_Client. Orders have an ID_Client field also and a relation to maintain integrity to Client's table by ID_Client field.

So when I want to join both tables i do:

SELECT 
    Orders.*, Clients.Name 
FROM 
    Orders 
INNER JOIN 
    Clients ON Clients.ID_Client = Orders.ID_Client

So if I took the job to create the primary key, and the relation between the tables,

Is there a reason why I need to explicitly include the joined columns in on clause?

Why can't I do something like:

SELECT 
    Orders.*, Clients.Name 
FROM 
    Orders 
INNER JOIN 
    Clients

So SQL should know which columns relate both tables...

Solution

  • I had this same question once and I found a great explanation for it on Database Administrator Stack Exchange, the answer below was the one that I found to be the best, but you can refer to the link for additional explanations as well.

    A foreign key is meant to constrain the data. ie enforce referential integrity. That's it. Nothing else.

    1. You can have multiple foreign keys to the same table. Consider the following where a shipment has a starting point, and an ending point.

      table: USA_States
StateID
StateName

table: Shipment
ShipmentID
PickupStateID Foreign key
DeliveryStateID Foreign key

      You may want to join based on the pickup state. Maybe you want to join on the delivery state. Maybe you want to perform 2 joins for both! The sql engine has no way of knowing what you want.

    2. You'll often cross join scalar values. Although scalars are usually the result of intermediate calculations, sometimes you'll have a special purpose table with exactly 1 record. If the engine tried to detect a foriegn key for the join.... it wouldn't make sense because cross joins never match up a column.

    3. In some special cases you'll join on columns where neither is unique. Therefore the presence of a PK/FK on those columns is impossible.

    4. You may think points 2 and 3 above are not relevant since your questions is about when there IS a single PK/FK relationship between tables. However the presence of single PK/FK between the tables does not mean you can't have other fields to join on in addition to the PK/FK. The sql engine would not know which fields you want to join on.

    5. Lets say you have a table "USA_States", and 5 other tables with a FK to the states. The "five" tables also have a few foreign keys to each other. Should the sql engine automatically join the "five" tables with "USA_States"? Or should it join the "five" to each other? Both? You could set up the relationships so that the sql engine enters an infinite loop trying to join stuff together. In this situation it's impossible fore the sql engine to guess what you want.

    In summary: PK/FK has nothing to do with table joins. They are separate unrelated things. It's just an accident of nature that you often join on the PK/FK columns.

    Would you want the sql engine to guess if it's a full, left, right, or inner join? I don't think so. Although that would arguably be a lesser sin than guessing the columns to join on.