\n| \n FALSE<\/p>\n<\/td>\n | \n TRUE<\/p>\n<\/td>\n | \n TRUE<\/p>\n<\/td>\n | \n UNKNOWN<\/b><\/p>\n<\/td>\n<\/tr>\n\n| \n UNKNOWN<\/p>\n<\/td>\n | \n UNKNOWN<\/p>\n<\/td>\n | \n FALSE<\/p>\n<\/td>\n | \n UNKNOWN<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\n\n| \n IMPLIES-2 <\/b><\/p>\n<\/td>\n | \n TRUE<\/p>\n<\/td>\n | \n FALSE<\/p>\n<\/td>\n | \n UNKNOWN<\/p>\n<\/td>\n<\/tr>\n | \n| \n TRUE<\/p>\n<\/td>\n | \n TRUE<\/p>\n<\/td>\n | \n TRUE<\/p>\n<\/td>\n | \n TRUE<\/p>\n<\/td>\n<\/tr>\n | \n| \n FALSE<\/p>\n<\/td>\n | \n TRUE<\/p>\n<\/td>\n | \n TRUE<\/p>\n<\/td>\n | \n TRUE<\/b><\/p>\n<\/td>\n<\/tr>\n\n| \n UNKNOWN<\/p>\n<\/td>\n | \n UNKNOWN<\/p>\n<\/td>\n | \n UNKNOWN<\/p>\n<\/td>\n | \n UNKNOWN<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Oops!\u00a0 They are not the same.\u00a0 The premise “X IMPLIES {FALSE, UNKNOWN}” could argue that a FALSE premise X can imply anything, TRUE, FALSE or UNKNOWN.\u00a0 Or that propagation of unknown values applies, so a FALSE premise can lead to a TRUE conclusion. Make a decision and design a logical system for yourself.\u00a0<\/p>\n We avoided it in the SQL Standards. We did not do implication and the other rules.\u00a0 This is a problem for those of us doing an optimizer for SQL.\u00a0<\/p>\n BIT Flags versus Predicates <\/b><\/h1>\n<\/b>One of the major problems for those people learning SQL is that they have not un-learned a mindset based originally on punch cards and magnetic tapes instead of RDBMS.\u00a0 Even if they never saw a punch card or a magnetic tape, they think of data processing as a sequence of procedural steps that depend on sequentially ordered data.\u00a0 At each step the data is moved to another file, scratch tape or temporary table.<\/p>\n Did you ever think about the people who use a singular table name instead of a collective or plural name?\u00a0 In a file system, you process one record at time.\u00a0 Calling the current record “Employee” makes sense.\u00a0 But in RDBMS, we work with the entire table<\/i> (set) all at once.\u00a0 Therefore we call that table “Personnel” instead.\u00a0<\/p>\n We used bit flags in the Dark Ages because we lacked storage and computing power.\u00a0 We would discover a fact that was TRUE at one point in time and then set a flag to mark that state within the system.\u00a0 A classic example is a deletion flag at the start of a tape file record.\u00a0 When the system read it, it would skip forward on the tape to the next record.\u00a0 Eventually, the deleted records would be removed when the data was copied over to a new tape.\u00a0<\/p>\n An embarrassing story of my own was writing a small restaurant scheduling program in BASIC on an early Personal Computer.\u00a0 I used a flag to show if the staff member was at least 18 years old which was the legal age to consume and serve alcoholic beverages at the time.\u00a0 The legal age then changed to 21 and my program cheerfully scheduled an entire shift of now under-aged servers on the night that the state Alcoholic Beverage Control agency decides to check on the new law.\u00a0 My client was not happy.\u00a0<\/p>\n If I had SQL back then, I would have used the employee birthdates to set up the shifts.\u00a0 A simple two keystrokes in a VIEW declaration would have done it.\u00a0<\/p>\n SQL is a “predicate language” by its nature in the same way that FORTRAN is an “algebra language” by its nature.\u00a0 Because the data is shared, and in constant motion, you cannot ever trust that the flags will actually show the current state of the data.\u00a0 You have to test it when you do something. Silly bit-flags work in file systems because nobody else can physically read the tape as it goes thru its sequential process.\u00a0<\/p>\n DDL versus DML <\/b><\/h1>\nAs if all of this was not weird enough, SQL’s 3VL behaves differently in the DDL (Data Declaration Language) and the DML (Data Manipulation Language).\u00a0<\/p>\n When you declare a column in a table in the DDL, you have the options of making in NOT NULL, giving it a DEFAULT value, using it in a CHECK() constraint and\/or adding DRI actions.\u00a0 Those options are one of the many <\/i>reasons that a column in RDBMS is different from a field in a file system.\u00a0<\/p>\n When I use a CHECK() constraint in the DDL and some or all of the columns are NULL-able, the search condition can return UNKNOWN.\u00a0 This is fine because DDL accepts {TRUE, UNKNOWN} as the same and will permit the data to persist.\u00a0<\/p>\n We did that because adding the extra search conditions to test for NULLs would be a screaming nightmare.\u00a0 Try it.\u00a0<\/p>\n In DML, we are not so forgiving.\u00a0 DML sees {FALSE, UNKNOWN} as the same and will reject the data, keeping only that which tests TRUE.\u00a0<\/p>\n This can be weird.\u00a0 You put something into a table and you need to work extra hard to get it back out because of NULLs and 3VL.\u00a0 That is why we SQL pros keep telling you to not make a column NULL-able unless it makes sense in the data model.\u00a0<\/p>\n Shorthand<\/b><\/h1>\nSQL is a higher level language so it has shorthands in it.\u00a0 But they can all be expanded out into combinations of {AND, OR, NOT}.\u00a0 Beginners in SQL often do not know how to use them and so tend to stick with the familiar {AND, OR, NOT} constructs they know from earlier languages like FORTRAN or BASIC. This is a bad way to write code.\u00a0 It hides the higher level logic and in better SQL engines, the shorthands are optimized.\u00a0\u00a0<\/p>\n BETWEEN<\/b><\/h3>\n\n This search condition is equivalent to two comparisons:<\/p>\n (x BETWEEN a AND b) = ((a < = x) AND (x < = b))<\/code><\/p>\nThe early optimizers simply expanded it out this way.\u00a0 More sophisticated optimizers look at it as a 3-ary logical operator and optimize for the “between-ness” that seems to be expected<\/p>\n<\/div>\n IN()<\/h3>\n\n <\/b>The IN()n search condition has two forms.\u00a0 \u00a0\u00a0<\/p>\n <expression> [NOT] IN (<expression list>)<\/code>
\n<expression> [NOT] IN (<single column select stmt>)<\/code><\/p>\nThis is a bit tricky.\u00a0 With either version we get a single column of expressions and compare the left hand expression to each of those values with an OR.\u00a0 In effect,<\/p>\n x IN (1, 2, 3) <\/code><\/p>\n…is shorthand for …<\/p>\n ((x = 1) OR (x = 2) OR (x = 3))<\/code><\/p>\nLikewise,<\/p>\n x NOT IN (1, 2, 3) <\/code><\/p>\n… is shorthand for …<\/p>\n NOT ((x = 1) OR (x = 2) OR (x = 3))<\/code><\/p>\nWhich can be re-written with DeMorgan’s law to:<\/p>\n ((x <> 1) AND (x <> 2) AND (x <> 3))<\/code><\/p>\nNow try to follow the same rule with<\/p>\n x IN (1, 2, NULL) <\/code><\/p>\n…is shorthand for…<\/p>\n ((x = 1) OR (x = 2) OR (x = NULL))
\n((x = 1) OR (x = 2) OR UNKNOWN)\u00a0 -- propagate NULLs<\/code><\/p>\nLet’s assume that one of the ORs is TRUE. So the other is FALSE<\/p>\n (TRUE OR FALSE OR UNKNOWN)\u00a0
\n(TRUE OR UNKNOWN)\u00a0
\n(TRUE)\u00a0 <\/code><\/p>\nThe only way this can return FALSE would be for x to be something other than {1, 2}.\u00a0 The only way for it to return UNKNOWN would be for x to be NULL.\u00a0<\/p>\n Repeat the exercise with<\/p>\n x NOT IN (1, 2, NULL) <\/code><\/p>\n…is shorthand for…<\/p>\n NOT ((x = 1) OR (x = 2) OR (x = NULL))
\nNOT ((x = 1) OR (x = 2) OR UNKNOWN) -- propagate NULLs
\n((x <> 1) AND (x <> 2) AND NOT UNKNOWN) -- DeMorgan
\n(UNKNOWN) -- definition of AND<\/code><\/p>\nSo if this was used in DDL, rows would be allowed in the table, but will always be rejected by the DML.<\/p>\n<\/div>\n SOME|ANY()<\/b><\/h3>\n\n There is an underused generalization of the IN() of the form:<\/p>\n <expression> <theta op> [SOME | ANY] (<single column select stmt>)<\/code><\/p>\nThis is shorthand for a chain of ORs:<\/p>\n (<expression> <theta op> <expression_1>
\n\u00a0 OR <expression> <theta op> <expression_2>
\n\u00a0 OR...
\n<expression> <theta op> <expression_n>)<\/code><\/p>\nThere is no difference between SOME and ANY, but sometimes the search condition reads a little better to a human.\u00a0<\/p>\n The IN() search condition is the case where the <theta op> is equality.\u00a0 All of the 3VL problems you had with IN() are also here.<\/p>\n<\/div>\n ALL<\/h3>\n\n There is really, really underused (almost unknown) search condition that generalized a chain of ANDs:<\/p>\n <expression> <theta op> ALL (<single column select stmt>)<\/code><\/p>\nThis is shorthand for a chain of ANDs:<\/p>\n (<expression> <theta op> <expression_1>
\nAND <expression> <theta op> <expression_2>
\nAND...
\n<expression> <theta op> <expression_n>)<\/code><\/p>\nThe ALL search condition does not play well with the extrema functions (e.g. MAX, MIN).\u00a0<\/p>\n It is counter-intuitive at first that these two search condition are not the same in SQL:<\/p>\n x >= (SELECT MAX(y) FROM Foobar)
\nx >= ALL (SELECT y FROM Foobar)<\/code><\/p>\nBut you have to remember the rules for the extrema functions — they drop out all the NULLs before returning the greater, greatest,\u00a0or least values.\u00a0 The (<single column select stmt>) does not drop NULLs, so you can get them in the results.\u00a0 This can leave a NULL to give us an UNKNOWN.\u00a0<\/p>\n<\/div>\n EXISTS()<\/h3>\n\n The EXISTS() search condition is on of the very few Boolean operator we have in SQL.\u00a0 It returns {TRUE, FALSE} and never UNKNOWN. This is because it works at the table level and not at the column level.\u00a0 Compare this to COUNT(*) which returns the cardinality<\/i> of the whole table as opposed to COUNT(<expression><\/i>).\u00a0 They look alike but are totally different.\u00a0<\/p>\n<\/div>\nSummary <\/b><\/h1>\n <\/b>I tell people that they need to learn to think in sets to get good with SQL.\u00a0 Give up\u00a0your sequential, procedural record-at-a-time mindset and trade up to declarative set processing!\u00a0 See the light!\u00a0 Leave the darkness!\u00a0<\/p>\n I probably ought to spend more time on 3VL since it is also something they never saw before.\u00a0 And it is not easy.\u00a0<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":" The whole subject of the Three-Valued (also known as ternary, trivalent or 3VL) Logic of SQL tends to trip people up. This is hardly surprising in view of the fact that it involves an esoteric Polish mathematician and because it behaves differently in the DDL (Data Declaration Language) and the DML (Data Manipulation Language). In response to requests, Joe Celko comes to the rescue and makes it all seem simple.…<\/p>\n","protected":false},"author":96214,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143525],"tags":[4149,4150,4941,4942],"coauthors":[6781],"class_list":["post-579","post","type-post","status-publish","format-standard","hentry","category-learn","tag-learn-sql-server","tag-sql","tag-sql-and-exists","tag-three-value-logic"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/579","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/96214"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=579"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/579\/revisions"}],"predecessor-version":[{"id":72991,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/579\/revisions\/72991"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=579"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=579"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=579"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=579"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
| | | |