Microsoft SQL Server - Lesson 21: Introduction to Conjunctions and Disjunctions

	Introduction to Conjunctions and Disjunctions

Logical Conjunctions

Introduction

So far, we have stated the conditions one at a time. This made their interpretation kind of easy. Sometimes, you will need to test one condition that depends on, or is related to, another. Boolean algebra allows you to combine two conditions and use the result, or to test two conditions but consider if either is true.

Imagine you have a list of students and you want to study some statistics of your database. You already know that, to get a list of girls, you can filter the students based on the gender. You would execute a statement as:

SELECT StudentNumber, FirstName, LastName, Gender, City, ParentsNames
FROM   Registration.Students
WHERE  Gender = N'Female';

This would produce:

Introduction to Conjunctions and Disjunctions

Filter

On the other hand, imagine you want to get a list of students who live in Silver Spring. You would execute a statement as:

SELECT StudentNumber, FirstName, LastName, Gender, City, ParentsNames
FROM   Registration.Students
WHERE  City = N'silver spring';

This would produce:

Logical Conjunction

From these two results, notice that there is no relationship between the fact that one student is a female and the fact that a student lives in Silver Spring. Still, the SQL allows you to combine two condition and produce a result of what those two conditions have in common. This is referred to as a logical conjunction.

Practical Learning: Introducing Conjunctions and Disjunctions

Start the computer and log in
Launch Microsoft SQL Server and click Connect
In the Object Explorer, expand Databases. Make sure you have the FunDS1 database (if you don't have it, create it by opening the FunDS1.sql file (FunDS1.txt) and executing it)
Expand FunDS1 and expand its Tables node
Right-click Inventory.StoreItems and click Edit Top 200 Rows
Right-click the table in the middle window, position the mouse on Pane, and click Diagram
Right-click anywhere in the windows -> Pane -> Criteria
Right-click anywhere in the windows -> Pane -> SQL
In the SQL pane, delete TOP (200)
In the Criteria pane, click the first box under Alias and type Item #
Click the second box under Alias and type Created On
Click the fourth box under Alias and type Name/Description
Click the fifth box under Alias and type Unit Price
Click the sixth box under Alias and type %Discount
Right-click in the Diagram pane and click Execute SQL
To see the items made by Michael Kors, in the Criteria pane, click the box at the intersection of Manufacturer and Filter
Type Michael Kors
To see the result, on the Query Designer toolbar, click the Execute SQL button
To hide the column, in the Criteria pane, click the check box at the intersection of Manufacturer and Output to remove the check mark
To see the result, on the Query Designer toolbar, click the Execute SQL button
To show the column again, in the Criteria pane, click the check box at the intersection of Manufacturer and Output

Creating a Logical Conjunction

The simplest logical condition involves two columns of a table. You can create the conjunction visually or with code.

To visually create a logical conjunction, open a table in the Query Designer and display the Criteria pane. In the Criteria section, click the Filter box that corresponds to the first desired field and type the condition. Click the Filter box that corresponds to the second desired field and type the other condition. Then execute the query.

To create a logical conjunction in SQL, you use the AND operator to combine two conditions. The formula to follow is:

SELECT WhatColumn(s)
FROM   WhatObject
WHERE  Condition1 AND Condition2

WhatColumn(s) and WhatObject are the same way we have used them so far. The AND keyword is the operator that joins the conditions. Each condition is written as a SQL operation using the formula:

Column operator Value

The Condition1 is the first that would be examined. If the first condition is false, the whole WHERE statement is false and there is no reason to examine the second condition. If the first condition is true, then the second condition would be examined.

Based on this, suppose we want to get a list of female students who live in Silver Spring. The SQL statement used to get this list can be written as follows:

SELECT StudentNumber, FirstName, LastName, Gender, City, ParentsNames
FROM   Registration.Students
WHERE  Gender = N'female' AND City = N'silver spring';

We stated that each condition was separately evaluated. For this reason, to make the conjunction statement easier to read, each condition should be included in parentheses. Therefore, the above SQL statement can be written as follows:

SELECT StudentNumber, FirstName, LastName, Gender, City, ParentsNames
FROM   Registration.Students
WHERE  (Gender = N'female') AND (City = N'silver spring')

This would produce:

Conjunction

Practical Learning: Creating a Logical Conjunction

To see the list of items made by Calvin Klein, in the Criteria pane, replace Michael Kors with Calvin Klein
To see the result, right-click the Diagram pane and click Execute SQL
To see only items of size 10, click the box at the intersection of Size and Filter
Type 10
To see the result, right-click the Diagram pane and click Execute SQL
To hide the columns, in the Criteria pane, click the check box at the intersection of Manufacturer and Output to remove the check mark
Click the check box at the intersection of Size and Output to remove the check mark
To see the result, on the Query Designer toolbar, click the Execute SQL button
To show the column again, in the Criteria pane, click the check box at the intersection of Size and Output
Press Tab three times and press Delete to remove the filter of the Size
Click the check box at the intersection of Manufacturer and Output

Logical Disjunctions

Introduction

The logical conjunction is used to check that two conditions are true, in which case both conditions must be valid. In a logical conjunction, if either condition is false, the whole statement is false. Sometimes you have two conditions that are equally important, in which the validity of either condition is good enough to make the whole statement true. This type of statement is referred to as logical disjunction.

Besides their names, the conjunction and the disjunction have many logical differences. A logical conjunction needs two different fields. Although you can (there is no rule against it), if you use the same field for both conditions of a conjunction, the statement would produce nothing:

Conjunction

This means that you should always use more than one column. On the other hand, a logical disjunction can be performed on one field. In this case, you would ask the interpreter to find out if the field matches this or that value. This means that, among the many values that the field has, you want to isolate two values and consider only either of them.

Like a conjunction, a disjunction can also be performed on more than one field.

Creating a Logical Disjunction

As mentioned already, a logical disjunction can be used on one or more fields. You can create the disjunction visually or using code.

To visually create a logical disjunction, open a table in the Query Designer and display the Criteria pane. You have various options. In the Criteria section:

To use only one box, click it in the Filter column. Type the first condition, followed by OR, followed by the other condition. You can keep ORing the conditions in the same Filter box
To use more than one box:
- If you want to examine the value for only one column, click the Filter box that corresponds to one column and type the first condition. Click the box at the intersection of the field and the Or... column, then type the second condition
- If you want to examine that value in more than one column, click the Filter box that corresponds to one of the columns and type the first condition. Click the Or... box that corresponds to the second field and type the other condition

After entering the conditions, execute the query.

To create a logical disjunction in SQL, you use the OR operator between two conditions. The formula to follow is:

SELECT WhatColumn(s)
FROM   WhatObject
WHERE  Condition1 OR Condition2

WhatColumn(s) and WhatObject are the same we have used so far. The OR keyword is the operator that joins the conditions. Each condition is written as a SQL operation using the formula:

Column operator Value

In this case:

If you are examining the values of only one column, use it in both conditions but use the desired operator and the desired value in each condition. Here is an example that wants to get a list of students who live either in Bethesda or in Silver Spring:
```
SELECT StudentNumber, FirstName, LastName, Gender, City, ParentsNames
FROM   Registration.Students
WHERE  (City = N'bethesda') OR (City = N'silver spring');
```
If you are examining the values of different fields, use each on its own condition followed by the operator and the value of your choice. Here is an example that wants to get a list of students that either are female (regardless of where they live) or live in Silver Spring (regardless of their gender):
```
SELECT StudentNumber, FirstName, LastName, Gender, City, ParentsNames
FROM   Registration.Students
WHERE  (Gender = N'female') OR (City = N'silver spring');
```

Logical conjunctions and disjunctions operator differently. As mentioned already, in a logical conjunctions, both conditions must be true for the whole statement to be true. In a logical disjuction, the interpreter first examines the first condition. If that condition is true, it concludes that the whole statement is true and there is no need to examine the second condition. If the first condition is false, then it examines the second condition. If the second condition is true, even if the first condition was false, the whole statement is true. Consider the above first statement:

SELECT StudentNumber, FirstName, LastName, Gender, City, ParentsNames
FROM   Registration.Students
WHERE  (City = N'bethesda') OR (City = N'silver spring');

The interpreter would check the City value of a record:

If the record indicates that the student lives in Bethesda, that record would be included and the interpreter would move to the next record
If the record indicates that the student lives not in Bethesda (the value of the City column is not Bethesda), then the interpreter would apply the second condition, which consists of checking whether the student lives in Silver Spring instead:
1. If the student lives in Silver Spring, the record is valid
2. If the student doesn't live in Silver Spring, and the record has already indicated that the student doesn't live in Bethesda, then the record is rejected (it will not be included in the result)

The interpreter then moves to the next record.

Consider our second statement:

SELECT StudentNumber, FirstName, LastName, Gender, City, ParentsNames
FROM   Registration.Students
WHERE  (Gender = N'female') OR (City = N'silver spring');

Once again, the interpreter would check each record:

The interpreter first checks the value of the Gender column. If a record indicates that the student is Female, that record is valid and the interpreter moves to the next record
If the record indicates that the student is not Female (the value of the Gender column is not Female), then the interpreter checks the value of the City column:
1. If the record indicates that the student lives in Silver Spring, then the record is valid, even if the student is not Female
2. If the record indicates that the student doesn't live in Silver Spring, and the record has already indicated that the student is not female, the whole record is rejected and the interpreter moves to the next record

Practical Learning: Creating a Logical Disjunction

To see the items made by either Calvin Klein or Anne Klein, in the Criteria pane, click the box at the intersection of Manufacturer and Filter
Type N'Calvin Klein' OR N'Anne Klein'
To see the result, right-click the Diagram pane and click Execute SQL
In the Criteria pane, click Manufacturer, press Tab six times and type Calvin Klein
To see the items made by either Calvin Klein or Tommy Hilfiger, in the Criteria pane, click the box at the intersection of Manufacturer and the first Or...
Type Tommy Hilfiger
To see the result, right-click the Diagram pane and click Execute SQL
In the Criteria pane, click Manufacturer, press Tab six times and press Delete
Press Tab and press Delete

Topics on Logical Conjunctions and Disjunctions

Introduction

So far, for our introduction to conjunctions and disjunctions, we used only string-based columns. When it comes to string-based conditions, you can use the LIKE operator to either match a string exactly or specify an approximation (a character, a range of characters, or a sub-string). Here is an example:

SELECT StudentNumber, FirstName, LastName, Gender, City, ParentsNames
FROM   Registration.Students
WHERE  (Gender = N'male') AND (LastName LIKE N'%an');

This LIKE condition is meant to find all students whose last name end with an:

Using LIKE in a Logical Conjunction

Practical Learning: Using Conjunctions and Disjunctions in Strings

To see the items made either by a Klein (Calvin Klein or Anne Klein) or a Lauren (Ralph Lauren, Polo Ralph Lauren, or Lauren By Ralph Lauren), click the box at the intersection of Manufacturer and Filter
Type like %klein%
Press Tab and type like %lauren%
To see the result, right-click the Diagram pane and click Execute SQL

Conjunctions, Disjunctions, and NULL Fields

To include the ability to check whether a field is null, in its condition, apply IS NULL to the column's name.

Practical Learning: Checking for Nullity

To see the items made by Ralph Lauren, in the Criteria pane, replace klein with lauren
Press Tab and press Delete
To see the result, right-click one of the panes and click Execute SQL
Notice that some items don't show a discount (the DiscountRate field shows NULL)
To see only items that qualify for a discount, click the box at the intersection of DiscountRate and Filter
Type is null
To see the result, right-click one of the panes and click Execute SQL
In the intersection of DiscountRate and Filter, delete IS NULL
To see the list of items made by Coach, in the Criteria pane, replace LIKE N'%lauren%' by Coach

Conjunctions, Disjunctions, and the Right Values

Besides string-based fields, any column can participate in a logical conjunction or disjunction. For types other than strings, follow their rules:

If you are visually creating a condition, simply type the desired value in the Criteria pane and the studio would take care of identifying it
If you are creating the condition using code:
- If the field is string-based or date/time based, include its value in single-quotes
- If the column is numeric based, provide desired number value
- If the field is Boolean, use 0 for a false value and 1 for true

Practical Learning: Filtering for the Right Values

To see the items made by Coach and that cost at least 250, in the Criteria pane, click the box at the intersection of UnitPrice and Filter
Type !< 250
To see the result, right-click one of the panes and click Execute SQL
In the Criteria pane, delete >= 250
In the Criteria pane, click Manufacturer, press Tab six times and press Delete
To get a list of items made by Ralph Lauren, Kenneth Cole, or a Klein, type LIKE N'%lauren%' OR LIKE N'%kenneth cole%' OR LIKE N'%klein%'
To see the result, right-click one of the panes and click Execute SQL
In the Criteria pane, click Manufacturer, press Tab six times and press Delete
To see the items made by Ralph Lauren again, type like %lauren%

Negating a Condition

As seen in previous lessons, there are many ways you can negate a Boolean condition. Remember that, to negate IS NULL, you can use IS NOT NULL.

Probably the easiest way to negate a condition is to precede it with the NOT operator. Here is an example:

SELECT StudentNumber, FirstName, LastName, Gender, City, ParentsNames
FROM   Registration.Students
WHERE  (Gender = N'female') AND NOT (City = N'silver spring');
GO

Or better, include NOT and its statement in parentheses. Here is an example:

SELECT StudentNumber, FirstName, LastName, Gender, City, ParentsNames
FROM   Registration.Students
WHERE  (Gender = N'female') AND (NOT (City = N'silver spring'));
GO

In the same way, you can negate the conditions individually. You can also negate the whole statement. In this case, after WHERE, type NOT() and, in the parentheses, include the logical conjunction or disjunction.

Practical Learning: Negating a Condition

To see Ralph Lauren's items that qualify for a discount, in the Criteria pane, click the box at the intersection of DiscountRate and Filter
Type is not null
To see the result, right-click one of the panes and click Execute SQL

Combining Conditions

The SQL allows you to create statements as complex as you want by combining the logical conjunctions and/or disjunctions.

To visually combine conditions, you have various options. If you are combining somewhat independent conditions, type each in the desired Filter or Or... box. If you are creating logical disjunctions that must be evaluated in groups, type each disjunctions in it own Filter box

Using SQL, you can create as many conditions as possible. Here is an example:

SELECT StudentNumber, FirstName, LastName,
       Gender, City, ParentsNames
FROM   Registration.Students
WHERE  (Gender = N'male') AND (City = N'silver spring')
	   AND (LastName LIKE N'%n');
GO

If the statement contains only conjunctions, the interpreter would examine the first condition:

If the first condition is false, the whole statement is false. The record is rejected and the interpreter moves to the next record
If the first condition is true, the interpreter puts a positive flag and moves to the next condition:
- If the second condition is false, the whole statement is false and the interpreter moves to the next record
- If the second condition is true, the interpreter puts a second positive flag and moves to the next condition:
  - If the next condition is false, the whole statement is false and the interpreter moves to the next record
  - If the next condition is true, and if the statement contains only three conditions, the record is valid and will be included in the result

The interpreter follows these steps for as many conjunctions as are included in the statement. If the statement contains only disjunctions, the approach is logically different. The interpreter would examine the first condition:

If the first condition is true, the whole statement is true. The record is valid (the record will be included in the result) and the interpreter moves to the next record
If the first condition is false, the interpreter moves to the second condition:
- If the second condition is true, the whole statement is true and the record is valid (it will be included in the result). The interpreter moves to the next record
- If the second condition is false, the interpreter moves to the next condition:
  - If the next condition is true, the whole statement is true. The record is valid and will be included in the result
  - If the next condition is false, and if the statement contains only three conditions, the whole record is rejected (it will not be included in the result)

The interpreter follows these steps for as many conjunctions as are included in the statement.

You can create a statement that is a combination of logical conjunctions and disjunctions. To make the statement easier to read and because of precedence rules, you should group the conditions in parentheses. Consider the following example:

SELECT 	StudentNumber, FirstName, LastName,
       	Gender, City, ParentsNames
FROM   	Registration.Students
WHERE  	((Gender = N'female') AND (City = N'silver spring'))
	OR
	((Gender = N'male') AND (City = N'bethesda'));
GO

This statement is essentially one main logical disjunction that checks two logical conjunctions. The first conjunction asks the interpreter to get a list of girls who live in Silver Spring. The second conjunction wants a list of boys who live in Bethesda. In reality, the clerk wants a list of girls who live in Silver Spring and boys who live in Bethesda:

Combining Logical Conjunction and Disjunctions

In the same way, you can create complex conditions.

Practical Learning: Combining Conditions

To see the list of discounted items made either by Lauren or a Klein, click the box at the intersection of Manufacturer and the first Or...
Type like %lauren%
Press Tab and type like %klein%
Make sure the Filter box for DiscountRate displays IS NOT NULL.
Click the box at the intersection of DiscountRate and the first Or... box
Type is not null

Click an empty area in the Diagram pane and see the statement in the SQL pane:

SELECT  ItemNumber AS [Item #],
	DateEntered AS [Created On], 
	Manufacturer, 
	ItemName AS [Name/Description], 
	Size, 
	UnitPrice AS [Unit Price], 
        DiscountRate AS [%Discount]
FROM    Inventory.StoreItems
WHERE   (Manufacturer LIKE N'%lauren%') AND (DiscountRate IS NOT NULL)
        OR
        (Manufacturer LIKE N'%klein%') AND (DiscountRate IS NOT NULL)

To see the result, right-click the Diagram pane and click Execute SQL
Replace Kenneth Cole by Anne Klein
Close Microsoft SQL Server

Exercises

Lesson Summary Questions

What is the basic formula to join two conditions?

SELECT WhatColumn(s)
WHERE  Condition1 AND Condition2
FROM   WhatObject

SELECT WhatColumn(s)
FROM   WhatObject
WHERE  Condition1 AND Condition2

SELECT TableName
FROM   Column(s)
WHERE  Condition1 AND Condition2

WITH TableName
SELECT WhatColumn(s)
WHERE  Condition1 AND Condition2

WITH Condition1 AND Condition2
SELECT WhatColumn(s)
FROM   WhatObject

What is the basic formula to disjoin two conditions?

SELECT WhatColumn(s)
WHERE  Condition1 OR Condition2
FROM   WhatObject

SELECT TableName
FROM   Column(s)
WHERE  Condition1 OR Condition2

WITH TableName
SELECT WhatColumn(s)
WHERE  Condition1 OR Condition2

SELECT WhatColumn(s)
FROM   WhatObject
WHERE  Condition1 OR Condition2

WITH Condition1 OR Condition2
SELECT WhatColumn(s)
FROM   WhatObject

Answers

Answers
1. Wrong Answer
2. Right Answer
3. Wrong Answer
4. Wrong Answer
5. Wrong Answer
Answers
1. Wrong Answer
2. Wrong Answer
3. Wrong Answer
4. Right Answer
5. Wrong Answer