Using 'IN' with a Subquery in SQL Statements

Question

Are there any performance issues using the "IN" keyword in SQL statements where we can use JOINs?

SELECT xxx FROM xxx WHERE ID IN (SELECT Id FROM xxx) 

Answer 1

No, it's ok to use.

You can write the query above using IN, EXISTS in all RDBMSs, some also support INTERSECT.

Semantically, this is a semi-join, which "give me the rows from table A, where I have at least one match in table B." INNER JOIN - "give me all the relevant lines"

So, if TableA has 3 rows and TableB has 5 rows that correspond to:

• INNER JOIN - 15 lines
• half connection is 3 lines

This is why IN and EXISTS are being pushed by me and other types of SQL here: JOIN is erroneous, requires DISTINCT and will be slower.

EXISTS supports multiple JOIN columns, IN does not work in SQL Server (this happens in others).

Answer 2

Instead of a single item, you can use a group. I have had cases where I had the best response time using a connection. Usually when I join all rows using the primary key / foreign key relationship, and also with regard to the non-key column. Especially if there are several connections. IN can SOMETIMES force an index scan, and the connection will TYPICALLY use the search if it is going to PK. When you create the tables, arrange the primary keys so that they are in the same order and explicitly declare PK / FK relationships. Joining is not limited to PK / FK. But sharing a connection is a PK / FK interaction, and in this case, my experience of using a connection with aligned keys is better performance.

Answer 3

As you can read here , JOINS is faster than sub-selections.