title: Is it true that JOINS can be used everywhere to replace Subqueries in SQL
body: <p>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:</p>
  
  <p>The database has two tables: </p>
  
  <p><code>Users</code> (<strong>ID</strong>, Name, City) and Friendship (<strong><em>ID</em></strong>, <strong><em>Friend_ID</em></strong>)</p>
  
  <p><code>Goal</code>: 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.</p>
  
  <p>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).</p>
  
  <p>By using subquery, I am able to achieve this. Query looks like:</p>
  
  <pre><code>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%';
  </code></pre>
  
  <p>Example entries:</p>
  
  <pre><code>Users
  </code></pre>
  
  <p>Id = 1 Name = Jon City = Mumbai</p>
  
  <p>Id=2 Name=Doe  City=Mumbai</p>
  
  <p>Id=3  Name=Arun  City=Mumbai</p>
  
  <p>Id=4 Name=Prakash City=Delhi</p>
  
  <pre><code>Friendship
  </code></pre>
  
  <p>Id= 1 Friends_Id = 2</p>
  
  <p>Id = 2 Friends_Id=1</p>
  
  <p>Id = 2 Friends_Id = 3</p>
  
  <p>Id = 3 Friends_Id = 2</p>
  
  <p>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.</p>
  
  <p>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). </p>