CODEWITHSUNDEEP
mysqldatabasedatabase-design
CyberJunkie
CyberJunkie

Reputation: 22674

Join query explained

I have the following join query for returning users to which a user subscribed. The users table consists of a user's data, and subscriptions table holds a user_id and and the id of the user she/he is subscribed to in the following column.

SELECT  subscriptions.following, users.first_name, last_name 
FROM (
subscriptions
)
JOIN users ON  users.user_id =  subscriptions.following
WHERE  subscriptions.user_id = 62
ORDER BY subscribed DESC 
LIMIT 0 , 30

I get the following table structure

+-----------+-------------+------------+
| following | first_name  | last_name  |
+-----------+-------------+------------+
| 11        | Dave        | Green      |
| 2         | Anna        | Blue       |
+-----------+-------------+------------+

Can someone please confirm if my query is correct? I don't understand in join queries why I have to specify FROM when I'm already telling which tables I'm using in the SELECT portion.

Note: The query works. I'm just not sure if there's a better way to do it.

Upvotes: 0

Views: 185

Answers (2)

HLGEM
HLGEM

Reputation: 96580

You have to understand that databases do not work straight through a query from top to bottom. It can't do the SELECT part until it has processed the join and the where clause and any group by clauses, etc. You also do not need to actually have data from every table joined on in the SELECT. For instance you might want a list of users who are employees (as opposed to customers). You therefore don't need anything except what is in the user table and are using the join simply to reduce the record set to only those that are also in the employee table. There is no requirement to select any fields from the employee table. The database also wouldn't know until you get to the joins how you want the data to be combined. You would get differnt records for these join conditions on the same table:

FROM subscriptions 
JOIN users ON users.user_id =  subscriptions.following 

FROM subscriptions 
left JOIN users ON users.user_id =  subscriptions.following 

FROM subscriptions 
CROSS JOIN users

FROM subscriptions 
JOIN users ON users.referred by_id =  subscriptions.following

The first would give you records that are in both tables.

The second would give you records that are in the first table but not necessaraily in the second table. Depending on the data, this could be the same number of records or it could be a significantly different number of records.

The third would match all the records in the first table to all the records in the second table.

The fourth would give you all the subscriptions of the person who referred the user not the subscriptions of the user (yes I know this is a made up field, but some tables really do have multiple possible fields that they could be joined on and SQL has to account for that).

I hope you can see from the explanation why you need to specify the join in the FROM clause.

Upvotes: 2

Jagadeesan
Jagadeesan

Reputation: 1097

Check the following. - Your query also fine.

SELECT  subscriptions.following, users.first_name, users.last_name 
FROM subscriptions
JOIN users ON users.user_id =  subscriptions.following AND subscriptions.user_id = 62
ORDER BY subscribed DESC 
LIMIT 0 , 30

1.subscriptions next to select is to specify the fields parent table. for example if you have same field name on both table the parent name is used for reference.

2.Where is not necessary if you check with static value.

Upvotes: 0

Related Questions