Question regarding the SQL 'NOT EXISTS'.?
-
I have a query regarding the 'not exists' in SQL. An example of 'not exists' can be as follows: SELECT A.* FROM scott.emp A WHERE NOT EXISTS (SELECT B.deptno FROM scott.dept B where B.deptno = A.deptno) My question is, like we used a condition in the 'where' clause in the sql statement inside the bracket, in the same way can we use the where clause outside of the bracket? I mean can the "SELECT A.* FROM scott.emp A WHERE NOT EXISTS" be changed to something like: "SELECT A.* FROM scott.emp A WHERE A.name not like '%george%' and NOT EXISTS" which means i do not want to do any kind of search on all names that have 'george' in them. I am not sure whether i put the question well!!!
-
Answer:
Logically, the name not like '%george%' does the trick. If that is really what you are doing however, the DB will have to do a full scan of either the table or the index because of the leading wildcard. Also depending on dbms, case can be an issue. If you really want to be searching for text in a field, and performance is an issue, most dbms's have a full text indexing option. The syntax for those queries looks something like: WHERE contains(a.name,'"george"')=0
Vijaysha... at Yahoo! Answers Visit the source
Other answers
You are close; just drop the 'and not exists' in your proposed solution. SELECT A.* FROM scott.emp A WHERE A.name not like '%george%' That will do the trick.
If I understand you correctly, try this: SELECT * FROM scott.emp WHERE name NOT LIKE '%george%' AND deptno NOT IN (SELECT DISTINCT a.deptno FROM scott.emp A JOIN scott.dept B ON A.deptno = B.deptno) (Of course, if a foreign key constraint exists, you shouldn't get any hits)
Related Q & A:
- How To Check Whether An Email Exists?Best solution by Stack Overflow
- What Is The Name Of That Which Exists Beyond The Universe?Best solution by Astronomy
- How can I optimize this dynamic SQL query in oracle with PL/SQL?Best solution by docs.oracle.com
- How to check if table exists in MySql db?Best solution by Stack Overflow
- Question regarding having high blood pressure?Best solution by Yahoo! Answers
Just Added Q & A:
- How many active mobile subscribers are there in China?Best solution by Quora
- How to find the right vacation?Best solution by bookit.com
- How To Make Your Own Primer?Best solution by thekrazycouponlady.com
- How do you get the domain & range?Best solution by ChaCha
- How do you open pop up blockers?Best solution by Yahoo! Answers
For every problem there is a solution! Proved by Solucija.
-
Got an issue and looking for advice?
-
Ask Solucija to search every corner of the Web for help.
-
Get workable solutions and helpful tips in a moment.
Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.