What does the total exists of sql use to judge whether it exists or not?
sql:
select * from employees s where not exists
(select 1 from dept_emp d where d.emp_no=s.emp_no)
Inner table s:
exterior d:
explanation of exists on the Internet: exists is an external table as a loop loop, and each loop cycle queries the inner table again.
my question:
the exterior goes to the loop inner table through one of the fields. As long as there is a field, it exists? Whether the
or
appearance matches the inner table through the whole piece of data, does it exist? if so, it should not be select 1
anyway, I don"t understand what exists uses to determine whether it exists, a field of a row of records, a field of a row of records, or something else?
There are matching conditions in the
where statement, d.emp_no=s.emp_no
if you can find a record that meets the criteria, it means that exists is valid, and it has nothing to do with the selection in select, but it is customary to write the number 1, which can be replaced with the id field of the table or other constant values.
< H2 > exists < / H2 >
specify a subquery to detect the existence of rows. Go through the loop appearance, and then see if the record in the exterior is the same as the data in the inner table. The result is put into the result set on the match. The specific sql statement is as follows:
SELECT
`user`.*
FROM
`user`
WHERE
EXISTS (
SELECT
`order`.user_id
FROM
`order`
WHERE
`user`.id = `order`.user_id
)
when querying with the exists keyword, first of all, we query not the contents of the subquery, but the table of our main query, that is to say, the sql statement we execute first is:
SELECT `user`.* FROM `user`
then, according to each record of the table, execute the following statement to determine whether the condition after where is true:
EXISTS (
SELECT
`order`.user_id
FROM
`order`
WHERE
`user`.id = `order`.user_id
)
if true, return true if not valid, return false . If true is returned, the result of the row is retained, if false is returned, the row is deleted, and the resulting result is returned. It can also be compared with IN to distinguish the difference between IN and exists in
Sql sentences and its application
.
