A simple DB containing two tables could be:


CREATE TABLE Supplier ( SupplierID INT SupplierName VARCHAR PRIMARY KEY (SupplierID) )

(Assume each stockID has only one supplier)

A typical SQL query would be:

SELECT StockID, StockDesc FROM Stock, Supplier WHERE Stock.SupplierID = Supplier.SupplierID AND SupplierName = "Smiths";

My fundamental understanding of Database design alerts me to the fact that I've already established that there is a relationship between STOCK and SUPPLIER tables via the DDL. Why does SQL need to restipulate that fact again in the first WHERE clause?

  • 3
    Just because the relationship exists doesn't mean you want to use it in your query Apr 13, 2017 at 14:23
  • I just noticed your title is not the same question as the one you write in your post. Could you clarify which question you actually are asking? Apr 13, 2017 at 14:39
  • SQL as a language is rather verbose, repetitive and inflexible. There are many queries where you may think "this doesn't seem optimal", and you would be right (in my opinion). The answers to the question give reasons why, within the design of SQL, specifying the relationship is necessary or at least desirable. But one could envision a better language than SQL.
    – user82096
    Apr 13, 2017 at 14:53

3 Answers 3


Note: Assuming that this question is about foreign key constraints rather than the WHERE clause, as WHERE seems to be irrelevant to the question. I'm assuming you intended JOIN instead.

Foreign key references are constraints on modifying operations such as INSERT, UPDATE, and DELETE. When a constraint exists, it prevents those operations from making changes which might result in a data structure which violates that constraint; constraints serve the purpose of helping to enforce the integrity/validity of the data in a database by preventing invalid modifications.

Constraints are not so relevant to SELECT operations because SELECT is non-modifying, so there's nothing really to protect against.

The purpose of a SELECT statement is to retrieve rows from one or more tables. SELECT statements only return data from tables which you explicitly request to be queried. If you don't specify that you wish to retrieve data from a referenced table using a JOIN, then any entries referenced through a foreign key constraint will be excluded by default from your results.

  • Thank you. I assume that the statement "FOREIGN KEY (SupplierID) REFERENCES Supplier(SupplierID)" is, therefore, a 'Constraint' on the Stock and Supplier tables? Apr 13, 2017 at 14:51
  • 1
    @user3396486 Correct - it's a constraint which prevents you from entering any data in there which can't be resolved as a foreign key to the Supplier table, based on its SupplierID field. For example, if you tried to use a foreign key of 123456 in the Stock table, where no such corresponding row/record existed in the Supplier table with a SupplierID of 123456 then the statement would fail and the RDBMS would return an error instead. Apr 13, 2017 at 15:00
  • 1
    Referential integrity? Apr 14, 2017 at 16:34
  • 1
    @user3396486 That's probably a better name for it, so In a word, yes! Apr 14, 2017 at 16:44

Actually, some SQL environments support NATURAL JOIN which joins on all common columns between the two tables. Here's Oracle's document about it (other databases might support this differently - consult your own system's documentation):


If the tables COUNTRIES and CITIES have two common columns named COUNTRY and COUNTRY_ISO_CODE, the following two SELECT statements are equivalent:



In practice, I rarely see this being used.

I personally don't like it because it hides the relationships between the tables. Often I am working on databases that I did not create myself and I don't actually know all the underlying relationships unless I look them up. I prefer to list my join clauses explicitly, I find it makes the query clearer and easier to understand, despite being a little longer. But that's just my opinion. ;)


There will be queries you won't be able to answer if the relationship is required.

Give a list of all suppliers who don't have any stock.

Select s.SupplierName
from Supplier as s
left outer join Stock as k
on s.SupplierID = k.SupplierID
where k.StockID IS NULL;

I think value is having the code be a little more explicit instead of having too much default behavior. For someone reading your query, they really need to memorize or look up table design.

Salesforce.com has a form of SQL that has some Object-Oriented behavior, so the joins are implied because they conform to the design of their system as they see fit. For someone who understands standard SQL, it's a different way of learning and can be annoying.

To save on repeated coding, you could wrap your query with all the necessary/redundant join logic in a view or some other reusable database object that encapsulates this stuff.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.