Menu
How to count the result of this query?
SELECT id FROM table1 where col1 like '%abcd%' intersect SELECT id from table2 where col2 like '%efgh%' 
824
The SQL INTERSECT clause/operator is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement. This means INTERSECT returns only common rows returned by the two SELECT statements.
The SQL Server (Transact-SQL) INTERSECT operator is used to return the records that are in common between two SELECT statements or data sets. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results. It is the intersection of the two SELECT statements.
The INTERSECT clause in SQL is used to combine two SELECT statements but the dataset returned by the INTERSECT statement will be the intersection of the data-sets of the two SELECT statements. In simple words, the INTERSECT statement will return only those rows which will be common to both of the SELECT statements.
The WHERE clause can be used along with SQL COUNT() function to select specific records from a table against a given condition.
SELECT COUNT(*) FROM ( SELECT id FROM table1 where col1 like '%abcd%' intersect SELECT id from table2 where col2 like '%efgh%' ) I Where I is an alias for the 'derived' table. It doesn't do anything important in this instance, but needs to be there for SQL to recognize the syntax - otherwise, you'll get an "Incorrect syntax near ')'" error.
74
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
