CODEWITHSUNDEEP
sqljoin
user5818995
user5818995

Reputation:

Is it true that JOINS can be used everywhere to replace Subqueries in SQL

I heard people saying that table joins can be used everywhere to replace sub-queries. I tested it in my query, but found that appropriate data set was only retrieved when I used sub-queries. I was not able to get same data set using joins. I am not sure if what I found is right because I am a newcomer in RDBMS, thus not so much experienced. I will try to draw the schema (in words) of the database in which I was experimenting:

The database has two tables:

Users (ID, Name, City) and Friendship (ID, Friend_ID)

Goal: Users table is designed to store simple user data and Friendship table represents Friendship between users. Friendship table has both the columns as foreign keys, referencing to Users.ID. Tables have many-to-many relationship between them.

Question: I have to retrieve Users.ID and Users.Name of all the Users, which are not friends with a particular user x, but are from same city (much like fb's friend suggestion system).

By using subquery, I am able to achieve this. Query looks like:

SELECT ID, NAME 
FROM USERS AS U
WHERE U.ID NOT IN (SELECT FRIENDS_ID
                   FROM FRIENDSHIP,
                        USERS
                   WHERE USERS.ID = FRIENDSHIP.ID AND USERS.ID = x)
  AND U.ID != x AND CITY LIKE '% A_CITY%';

Example entries:

Users

Id = 1 Name = Jon City = Mumbai

Id=2 Name=Doe City=Mumbai

Id=3 Name=Arun City=Mumbai

Id=4 Name=Prakash City=Delhi

Friendship

Id= 1 Friends_Id = 2

Id = 2 Friends_Id=1

Id = 2 Friends_Id = 3

Id = 3 Friends_Id = 2

Can I get the same data set in a single query by performing joins. How? Please let me know if my question is not clear. Thanks.

Note: I used inner join in the sub-query by specifying both tables: Friendship, Users. Omitting the Users table and using the U from outside, gives an error (But if not using alias for the table Users, query becomes syntactically okay but result from this query includes ID's and names of users, who have more than one friends, including the user having ID x. Interesting, but is not the topic of the question).

Upvotes: 4

Views: 172

Answers (3)

vittore
vittore

Reputation: 17589

For not in you can use left join and check for is null:

select u.id, u.name 
from  Users u 
left join Friends f on u.id = f.id and f.friend_id = @person
where u.city like '%city%' and f.friend_id is null and u.id <> @person;

There are some cases where you can't work out your way with just inner/left/right joins, but your case is not one of them.

Please check sql fiddle: http://sqlfiddle.com/#!9/1c5b1/14

Also about your note: What you tried to do can be achieved with lateral join or cross apply depending on the engine you are using.

Upvotes: 3

Aheho
Aheho

Reputation: 12821

You can rewrite your query using only joins. The trick is to join to the User tables once with an inner join to identify users within the same city and reference the Friendship table with a left join and a null check to identify non-friends.

SELECT
     U1.ID,
     U1.Name
FROM
    USERS U1
INNER JOIN
    USERS U2
ON
     U1.CITY = U2.CITY
LEFT JOIN
     FRIENDSHIP F
ON
    U2.ID = F.ID AND
    U1.ID = F.FRIEND_ID
WHERE
     U2.id = X AND
     U1.ID <> U2.id AND
     F.id IS NULL

The above query doesn't handle the situation where USER x's primary key is in the FRIEND_ID column of the FRIENDSHIP table. I assume because your subquery version doesn't handle that situation, perhaps you create 2 rows for each friendship, or friendships are not bi-directional.

Upvotes: 1

tadamhicks
tadamhicks

Reputation: 925

Joins and subqueries can be used to achieve similar results in some cases, but certainly not all. As an example, this query with a subquery could not be achieve vis-a-vis a join:

SELECT ID, COLUMN1, COUNT(*) FROM MYTABLE
WHERE ID IN (
    SELECT DISTINCT ID FROM MYTABLE
    WHERE COLUMN2 NOT IN (VALUES1, VALUES2)
)
GROUP BY ID;

This is only one example, but there are many.

Conversely, you cannot get information from another table by using a subquery without joining it.

As to your example

SELECT ID, NAME FROM USERS AS U 
WHERE U.ID NOT IN (
    SELECT FRIENDS_ID FROM FRIENDSHIP, USERS 
    WHERE USERS.ID = FRIENDSHIP.ID AND USERS.ID = x) 
AND U.ID != x AND CITY LIKE '% A_CITY%';

This could be constructed as:

select ID, NAME from users u
join FRIENDSHIP f        on f.ID = u.ID
where u.ID = x
and u.ID != y
and CITY like '%A_CITY';

I changed your second x to a y assumptively, so it wouldn't cause confusion.

Of course, you may also want to LEFT JOIN aka LEFT OUTER JOIN if there is a chance that there may be multiple results in the FRIENDSHIP table.

Upvotes: -2

Related Questions