CODEWITHSUNDEEP
sql
chris05
chris05

Reputation: 745

Selecting multiple tables

This is a hypothetical (maybe even a naive) example without using joins. Consider you have a users table and you have to list all users assigned to a particular role. A SQL statement could be expressed as:

SELECT u.username
FROM users u, roles r
WHERE (u.roleid = r.id AND r.id = 5);

If you assume that a relation exists, can the following mean the same thing or a join of some sort is required?:

SELECT u.username
FROM users u, roles r
WHERE r.id = 5;

Upvotes: 2

Views: 103

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

No. In the second version you have no relationship between the tables. The , operator in the from clause means cross join. The second example will either return all users at least once (depending on the number of matched in the second table). Or it will return no rows (if there are no matches in the second table).

If the second example were:

SELECT u.username
FROM users u, roles r
WHERE r.id = 5 and u.id = 5

Then they would mean the same thing. The clearer and better way to write this is:

SELECT u.username
FROM users u cross join roles r
WHERE r.id = 5 and u.id = 5

Or using proper inner join syntax:

SELECT u.username
FROM users u join
     roles r
     on r.id = u.id
WHERE r.id = 5  /* this could also be in the `on` clause */

Upvotes: 0

AllTooSir
AllTooSir

Reputation: 49372

You need two columns of the same type one for each table to JOIN .You need the join-predicate ON u.roleid = r.id to get the correct data .

Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row. The result of the join can be defined as the outcome of first taking the Cartesian product (or Cross join) of all records in the tables (combining every record in table A with every record in table B)—then return all records which satisfy the join predicate.

Upvotes: 0

user359040
user359040

Reputation:

The second query is an example of an implicit cross join (aka Cartesian join) - every record from users will be joined to every record from roles with id=5, since all these combinations will have the where clause evaluate as true.

Upvotes: 2

Fabio
Fabio

Reputation: 23500

A join will be required to have correct data returned

SELECT u.username
FROM users u
JOIN roles r
ON u.roleid = r.id
WHERE r.id = 5;

I think is better to use explicit join with ON to dtermine which columns have relationship rather than using realtionship in WHERE clause

Upvotes: 0

Related Questions