-1

A simple DB containing two tables could be:

CREATE TABLE Stock ( StockID INT StockDesc VARCHAR SupplierID INT PRIMARY KEY (StockID) FOREIGN KEY (SupplierID) REFERENCES Supplier(SupplierID))

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 – Dan Pichelman Apr 13 '17 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? – FrustratedWithFormsDesigner Apr 13 '17 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 '17 at 14:53
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? – user3396486 Apr 13 '17 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. – Ben Cottrell Apr 13 '17 at 15:00
  • 1
    Referential integrity? – user3396486 Apr 14 '17 at 16:34
  • 1
    @user3396486 That's probably a better name for it, so In a word, yes! – Ben Cottrell Apr 14 '17 at 16:44
2

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):

http://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqljnaturaljoin.html

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

SELECT * FROM COUNTRIES NATURAL JOIN CITIES

SELECT * FROM COUNTRIES JOIN CITIES USING (COUNTRY, COUNTRY_ISO_CODE)

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. ;)

1

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, privacy policy and cookie policy

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