SQL Select all columns from multiple tables

Question

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?

Its very correct. It will return you the cross product of these tables. — Ankit Bajpai, Jun 27 '14 at 06:41
Does any of this answer your question? [SQL SELECT from multiple tables](https://stackoverflow.com/questions/1416003/sql-select-from-multiple-tables) or [SELECT \* FROM multiple tables. MySQL](https://stackoverflow.com/questions/12890071/select-from-multiple-tables-mysql) — user8395964, Jun 26 '23 at 16:38

score 1 · Answer 1 · answered Jun 27 '14 at 06:40

1

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

answered Jun 27 '14 at 06:40

Azar

1,852
15
17

score 0 · Answer 2 · answered Jun 27 '14 at 06:43

0

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

answered Jun 27 '14 at 06:43

Akhilesh Chaurasiya

1
1

score 0 · Answer 3 · answered Jun 27 '14 at 06:45

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

score 0 · Answer 4 · answered Jun 27 '14 at 06:47

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.

SQL Select all columns from multiple tables

4 Answers4