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

Question

I heard people say that table joins can be used everywhere to replace subqueries. I tested it in my query, but found that the corresponding dataset was only received when I used subqueries. I could not get the same dataset using joins. I’m not sure that what I found is correct, because I am new to RDBMS, so I have not survived so much. I will try to draw a diagram (in words) of the database in which I experimented:

There are two tables in the database:

Users( ID , name, city) and friendship ( ID , Friend_ID )

Goal: The user table is for storing simple user data, and the friendship table is friendship between users. The friendship table has both columns as foreign keys that reference User.ID. Tables have a many-to-many relationship between them.

Question: I need to get Users.ID and Users.Name of all users who are not familiar with a specific user x, but from the same city (the same as the fb friend suggestion system).

Using a subquery, I can achieve this. The request looks like this:

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

, . ? , , . .

. , : , . "" U , ( "", , , , x. , ).

Answer 1

not in left join 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;

, // , .

, sql: http://sqlfiddle.com/#!9/1c5b1/14

: , , lateral join cross apply .

Answer 2

, . , User , , .

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

, USER x FRIEND_ID FRIENDSHIP. , , , 2 , .

Answer 3

, , , . :

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

, .

, , , .

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%';

:

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';

x y, .

Of course, you can also use LEFT JOIN aka LEFT OUTER JOIN if there is a possibility that there can be several results in the FRIENDSHIP table.