CODEWITHSUNDEEP
sqlsql-server
Marilee
Marilee

Reputation: 1598

SQL Select all columns from multiple tables

A question in an old exam paper reads as follows:

Suppose you have 3 tables (CARDHOLDERS, CARDHOLDER_STATUS, ACCOUNTS). Write a query to bring back all possible combinations (cross product) of the data.

Now my guess is the query will look something like this

SELECT * FROM CARDHOLDERS, CARDHOLDER_STATUS, ACCOUNTS

Will my above query return the correct results, or am I completely lost?

Upvotes: 2

Views: 8299

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Yes this is correct. It is however recommended to use the keyword CROSS JOIN instead of commas (just like you would write INNER JOIN, LEFT JOIN and so on) to make clear what you are doing and that you are doing that on purpose.

SELECT * FROM CARDHOLDERS, CARDHOLDER_STATUS, ACCOUNTS;

Here a reader may think you forgot the WHERE clause or removed it accidentally, so the tables lost there join criteria involuntarily.

SELECT * FROM CARDHOLDERS CROSS JOIN CARDHOLDER_STATUS CROSS JOIN ACCOUNTS;

Here the reader can be sure you actually wanted to cross join the tables, because you explicitely say so.

As a rule: Never join tables by listing them comma-separated. This is a syntax made obsolete some twenty years ago. Always use explicit join syntax instead. It is less prone to errors and more readable.

Upvotes: 0

Kristof
Kristof

Reputation: 3315

This query will indeed give all cardholders and per cardholder every calhoderstatus and per every cardholder + cardholder status it will then fetch every account.
So if you have 2 cardholders, 2 statusses and 10 accounts the query will return 2x2x10 = 40 results.

Usually you don't want all the matching records but the ones that are relevant.
Suppose you have a status deleted and a status active then every cardholder will only have 1 status.
Currently your query will return this (not including all data, too much type work ;)):

Cardholder 1 -- Status Deleted
Cardholder 1 -- Status Active
Cardholder 2 -- Status Deleted
Cardholder 2 -- Status Active

If you now add a a statusID to the cardholder records you can specify which status a cardholder has by writing your query like this : 

SELECT * 
FROM CARDHOLDERS, CARDHOLDER_STATUS, ACCOUNTS
WHERE CARDHOLDERS.StatusID = CARDHOLDER_STATUS.ID



This will return the following data(and might be what you need):
Cardholder 1 -- StatusID 1 - Status Active
Cardholder 2 -- StatusID 2 - Status Deleted

Upvotes: 0

Akhilesh Chaurasiya
Akhilesh Chaurasiya

Reputation: 1

Yes you are right this will produce the cross join for you.

you can also try this syntax for same results

SELECT * FROM CARDHOLDERS Cross join CARDHOLDER_STATUS Cross join ACCOUNTS

Upvotes: 0

Azar
Azar

Reputation: 1867

Above one is right . You can also try 

SELECT * FROM CARDHOLDERS cross apply  CARDHOLDER_STATUS cross apply ACCOUNTS

or 

SELECT * FROM CARDHOLDERS cross join  CARDHOLDER_STATUS cross join ACCOUNTS

Upvotes: 1

Related Questions