#MYSQL JOIN TABLES CHECK FOR PRECENCE IN A TABLE UPDATE#
UPDATE (3 years on): I've since flipped to preferring NOT IN (subquery) because it is more readable and if you are worried about unexpected results with nulls (and you should be) then stop using them entirely, I did many more years ago. SELECT *Īs with NOT IN (subquery) (same for the outer join approach), you need to take extra care if the WHERE clause within the subquery involves nulls (hint: if WHERE clause in the subquery evaluates UNKNOWN due to the presence of nulls then it will be coerced to be FALSE by EXISTS, which may yield unexpected results). Personally, I prefer to use EXISTS in SQL for semi difference join because the join clauses are closer together in the written code and doesn't result in projection over the joined table e.g. Using EXCEPT (equivalent to MINUS in Oracle) is another possible approach if your SQL product supports it and again depending on the data (specifically, when the headings of the two tables are the same). The most commonly seen are: outer join with a test for nulls in the WHERE clause, closely followed by EXISTS or IN (subquery). Semi difference can of course be written using other SQL predicates. Standard SQL-92 doesn't have one (it has a MATCH (subquery) semijoin predicate but, although tempting to think otherwise, the semantics for NOT MATCH (subquery) are not the same as for semi difference FWIW the truly relational language Tutorial D successfully uses the NOT MATCHING semi difference). Most SQL products lacks an explicit semi difference operator or keyword.
The relational operator you require is semi difference a.k.a.
0 kommentar(er)
