When using exists, it can sometimes improve the query speed if it is used correctly:
1, use exists instead of inner join
2, use exists instead of in
1, use exists instead of INNER join example:
You typically encounter the following statements when you write SQL statements in general:
Two tables when connecting, take a table of data, the general wording through the Association query (inner JOIN):
Select a.ID, A.workflowid,a.operator,a.stepid
FROM dbo. [[zping.com]] A
INNER JOIN Workflowbase B on a.workflowid=b.id
and operator= ' 4028814111ad9dc10111afc134f10041 '
Query Result:
(1327 rows affected)
Table ' worktable '. Scan count 0, logical read 0 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' Workflowbase '. Scan count 1, logical read 293 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' [zping.com] '. Scan count 1, logical read 1339 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
There is also a way to use exists to fetch data
Select A.id,a.workflowid,a.operator, A.stepid
FROM dbo. [[[Zping.com]]] a where exists
(SELECT ' X ' from Workflowbase b where a.workflowid=b.id)
and operator= ' 4028814111ad9dc10111afc134f10041 '
Execution Result:
(1327 rows affected)
Table ' [zping.com] '. Scan count 1, logical read 1339 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' Workflowbase '. Scan count 1, logical read 291 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Here are two IO times,EXISTS less than inner join 2 IO, compared to the execution plan cost, look at the difference between the two:
At this point we find that using exists is slightly more efficient than inner join.
2, use exists instead of in
Requirement: The ID in the Write Workflowbase table is not in the table dbo. [[zping.com]] The line:
The general wording:
SELECT * FROM Workflowbase
where ID not in (
Select A.workflowid
FROM dbo. [[[Zping.com]]] a)
Execution Result:
(1 rows affected)
Table ' worktable '. Scan count 0, logical read 0 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' [zping.com] '. Scan Count 5, logical read 56,952 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' Workflowbase '. Scan count 3, logical read 1589 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' worktable '. Scan count 0, logical read 0 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Use EXISTSL to write:
SELECT * from Workflowbase b
Where NOT EXISTS (
Select ' X '
FROM dbo. [[[Zping.com]]] a where a.workflowid=b.id)
Take a look at the results
(1 rows affected)
Table ' worktable '. Scan count 0, logical read 0 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' [zping.com] '. Scan count 3, logical read 18,984 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' Workflowbase '. Scan count 3, logical read 1589 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Two IO gap:56952+1589=58,541 times (using in)
18984+1589=20573 times (using exists)
Using exists is 2.8 times times the in and query performance is greatly improved.
EXISTS makes queries faster because the RDBMS core module returns results immediately after the conditions of the subquery are met.
SQL optimization--use EXISTS instead of in and inner join to select the correct execution plan
