How to avoid writing a subquery?
Q: How to do the query “select * from tbl1 where tbl1.fname like ‘John’ and lname not in (select lname from tbl1 where ybirth>1975 and city=’London’)” without the subquery?
Kevin Kline says: This is a very simple SQL problem. The following query can be rewritten:
SELECT *
FROM tbl1
WHERE tbl1.fname LIKE ‘John’
AND lname NOT IN (SELECT lname
FROM tbl1
WHERE ybirth>1975 AND city=’London’)
As:
SELECT *
FROM tbl1
WHERE tble1.fname = ‘JOHN’
AND (ybirth <> 1975
AND city <> ‘LONDON’)
Note that you don’t need the LIKE operator since you didn’t use a wildcard operator in the name ‘JOHN’. Also note that ybirth must not be a datetime datatype or else the query would need to say “…AND ybirth <> ‘JAN-01-1975…’ and otherwise follow a proper datetime format.
Technorati Tags:
SQL Server, SQL development, LIKE operators
