CODEWITHSUNDEEP
sqljoin
SGoldwin
SGoldwin

Reputation: 131

One select, two tables and no join

I stumble upon below sql-code (translated for sample purpose) :

SELECT m.username, c.companyname 
FROM member m , company c 
WHERE m.id = 5 AND c.id = 10;

Table members has id as PK. Company has id as PK. There is no relation between these two tables. At first I didn't think this would work, but it did.

Result is one row with value for username and companyname.

Before I saw this solution I would have gone for 2 sql - one for member and one for company.

What is the possible danger with this SQL?

Upvotes: 0

Views: 62

Answers (2)

evilive
evilive

Reputation: 996

If you select from more than one table without joining them you get all possible combinations as result. In this case due to pk on both sides, you have only on result.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269663

This SQL is equivalent to:

SELECT m.username, c.companyname
FROM member m CROSS JOIN company c
WHERE m.id = 5 AND c.id = 10;

The comma and the cross join produce a cartesian product. The where clause is presumably choosing one row from each table. I much prefer the CROSS JOIN method and have a simple rule: Never use commas in the from clause.

Upvotes: 3

Related Questions