Logical Operators – SQL Server Syntax Example: Logical Operators – T-SQL Example

3-Minute Video Tour of LearnVisualStudio.NET by Bob Tabor


Learn Visual Studio Video Tour



Ideal Tools Organizer


Premium (Not Free) Video Tutorials

Free Video Tutorials & Free Tools

Logical Operators – SQL Server Syntax Example: Logical Operators – T-SQL Example

Purpose: – Illustrates the for the .

SYNTAX:

SELECT [ ALL | DISTINCT ]
[TOP ( expression ) [PERCENT] [ WITH TIES ] ]
[ INTO new_table ]
[ FROM { table_source } [ ,...n ] ]
[ WHERE expression1 logical_operator expression2 ]
[ GROUP BY ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ]

PURPOSE:
Logical operators return true or false indicating indicating whether condition is true.

 

LOGICAL OPERATORS:

Operator Meaning
ALL TRUE if all of a set of comparisons are TRUE.
AND TRUE if both Boolean expressions are TRUE.
ANY TRUE if any one of a set of comparisons are TRUE.
BETWEEN TRUE if the operand is within a range including bounds.
EXISTS TRUE if a subquery contains any rows.
IN TRUE if the operand is equal to one of a list of expressions.
LIKE TRUE if the operand matches a pattern.
NOT Reverses the value of any other Boolean operator.
OR TRUE if either Boolean expression is TRUE.
SOME TRUE if some of a set of comparisons are TRUE.

 

Code Sample for Logical Operators:

 T-SQL |   |? 
 
/* Logical Operators example from http://idealprogrammer.com 
 
 PURPOSE: Logical operators return true or false indicating whether a condition is 
 true.  Here are the various types of operators (including logical): arithmetic,
 assignment, bitwise, comparison, logical, scope resolution, string concatenation,
 and unary.
 
 SYNTAX: 
 
 LOGICAL OPERATORS:
 
 Operator 	 Meaning
 
 ALL          TRUE if all of a set of comparisons are TRUE.
 
 AND          TRUE if both Boolean expressions are TRUE.
 
 ANY          TRUE if any one of a set of comparisons are TRUE.
 
 BETWEEN      TRUE if the operand is within a range including bounds.
 
 EXISTS       TRUE if a subquery contains any rows.
 
 IN           TRUE if the operand is equal to one of a list of expressions.
 
 LIKE         TRUE if the operand matches a pattern.
 
 NOT          Reverses the value of any other Boolean operator.
 
 OR           TRUE if either Boolean expression is TRUE.
 
 SOME         TRUE if some of a set of comparisons are TRUE.
 
 
 
 SELECT [ ALL | DISTINCT ] 
			[TOP ( expression ) [PERCENT] [ WITH TIES ] ] 
			 column_list, ISNULL(check_expression, replacement_value)  
			[ INTO new_table ] 
			[ FROM { table_source } [ ,...n ] ] 
			[ WHERE expression1 logical_operator expression2] 
			[ GROUP BY ] 
			[ HAVING  search_condition  ] 
			[ ORDER BY  order_expression [ ASC | DESC ] ] 
*/
 
 
CREATE TABLE people(
     ID          INT,
     firstname   VARCHAR (20),
     lastname    VARCHAR (20),
     statecode   VARCHAR (2),
     alive       BIT,
     height      INT )
 GO
 
 
 
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height) 
 VALUES (1,  'Paul', 'Revere', 'AL', 0, 74)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height) 
 VALUES (2,  'Pat', 'Lennon%', 'NY', 0, 69)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height)
 VALUES (3,  'Peter', 'Martin', 'NY', 1, 75)
 GO
 
 INSERT INTO people (ID, firstname, lastname, statecode, alive, height)
 VALUES (4,  'George', 'Washington', 'VA', 0, 75)
 GO
 
 
 
 -- Select entire table
 SELECT 'Entire Table', * FROM people
 
 -- 1. ALL requires that all rows in subquery be true - returns
 --    all rows where height is maximum
 SELECT 'Example #1' AS Example, firstname, lastname, height  
  FROM people p
  WHERE p.height >= ALL (SELECT p2.height FROM people p2) 
 
 GO
 
 -- 2. ANY requires any row in subquery to be true - returns all rows
 --    that are not maximum
 SELECT 'Example #2' AS Example, firstname, lastname, height  
  FROM people p
  WHERE p.height < ANY (SELECT p2.height FROM people p2) 
 
 GO
 
 -- 3. BETWEEN returns all rows that are within range including bounds
 SELECT 'Example #3' AS Example, firstname, lastname, height  
  FROM people p
  WHERE p.height  BETWEEN 74 and 75
 
 GO
 
 -- 4. LIKE - Using [] to select firstname that starts with P[ae]t%
 SELECT 'Example #4' AS Example, firstname, lastname, statecode  
  FROM people 
  WHERE firstname LIKE 'P[ae]t%' 
 
 GO
 
 
 
 
 
 DROP TABLE people;
 
 GO
 
 
 
 
 
 
 
 
 
 
 
 
 

Sample Output for Logical Operators Example

SQL Select Logical Operators

Related posts:

  1. EXISTS – SQL Server Syntax Example: EXISTS – T-SQL Example EXISTS - SQL Server Syntax Example: EXISTS - T-SQL Example...
  2. IN – SQL Server Syntax Example: IN – T-SQL Example IN - SQL Server Syntax Example: IN - T-SQL Example...
  3. LIKE – SQL Server Syntax Example: LIKE – T-SQL Example LIKE - SQL Server Syntax Example: LIKE - T-SQL Example...
  4. DISTINCT – SQL Server Syntax Example: DISTINCT – T-SQL Example DISTINCT - SQL Server Syntax Example: DISTINCT - T-SQL Example...
  5. HAVING – SQL Server Syntax Example: HAVING – T-SQL Example HAVING - SQL Server Syntax Example: HAVING - T-SQL Example...

Related posts brought to you by Yet Another Related Posts Plugin.

Speak Your Mind

Tell us what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!

Powered by WP Hashcash

This blog uses the cross-linker plugin developed by Web-Developers.Net