SQL Operator
By
ukmodak
|
March 31st 2024 10:32:28 AM
| viewed
516
times
SQL Operator
| Operator |
Description |
SQL |
| + |
Add |
SELECT 30 + 20; |
| - |
Subtract |
SELECT 30 - 20; |
| * |
Multiply |
SELECT 30 * 20; |
| / |
Divide |
SELECT 30 / 10; |
| % |
Modulo |
SELECT 17 % 5; |
SQL Bitwise Operators
| Operator |
Description |
SQL |
| & |
Bitwise AND |
select 1&1 |
| | |
Bitwise OR |
select 21 | 22 |
| ^ |
Bitwise exclusive OR |
select 21^22 |
SQL Comparison Operators
| Operator |
Description |
SQL |
| = |
Equal to |
SELECT * FROM Products
WHERE Price = 18; |
| > |
Greater than |
SELECT * FROM Products
WHERE Price > 30; |
| < |
Less than |
SELECT * FROM Products
WHERE Price < 30; |
| >= |
Greater than or equal to |
SELECT * FROM Products
WHERE Price >= 30; |
| <= |
Less than or equal to |
SELECT * FROM Products
WHERE Price <= 30; |
| <> |
Less than or equal to |
SELECT * FROM Products
WHERE Price <> 18; |
SQL Compound Operators
| Operator |
Description |
SQL |
| += |
Add equals |
DECLARE @x1 int = 27;
SET @x1 += 2 ;
SELECT @x1 AS Added_2; |
| -= |
Subtract equals |
DECLARE @x2 int = 27;
SET @x2 -= 2 ;
SELECT @x2 AS Subtracted_2;
|
| *= |
Multiply equals |
DECLARE @x3 int = 27;
SET @x3 *= 2 ;
SELECT @x3 AS Multiplied_by_2; |
| /= |
Divide equals |
DECLARE @x4 int = 27;
SET @x4 /= 2 ;
SELECT @x4 AS Divided_by_2; |
| %= |
Modulo equals |
DECLARE @x5 int = 27;
SET @x5 %= 2 ;
SELECT @x5 AS Modulo_of_27_divided_by_2; |
| &= |
Bitwise AND equals |
DECLARE @x6 int = 9;
SET @x6 &= 13 ;
SELECT @x6 AS Bitwise_AND; |
| ^-= |
Bitwise exclusive equals |
DECLARE @x7 int = 27;
SET @x7 ^= 2 ;
SELECT @x7 AS Bitwise_Exclusive_OR; |
| |*= |
Bitwise OR equals |
DECLARE @x8 int = 27;
SET @x8 |= 2 ;
SELECT @x8 AS Bitwise_OR; |
SQL Logical Operators
| Operator |
Description |
SQL |
| ALL |
TRUE if all of the subquery values meet the condition |
SELECT * FROM Products
WHERE Price > ALL (SELECT Price FROM Products WHERE Price > 500); |
| AND |
TRUE if all the conditions separated by AND is TRUE |
SELECT * FROM Customers
WHERE City = "London" AND Country = "UK"; |
| ANY |
TRUE if any of the subquery values meet the condition |
SELECT * FROM Products
WHERE Price > ANY (SELECT Price FROM Products WHERE Price > 50); |
| BETWEEN |
Divide equals |
SELECT * FROM Products
WHERE Price BETWEEN 50 AND 60; |
| EXISTS |
TRUE if the subquery returns one or more records |
SELECT * FROM Products
WHERE EXISTS (SELECT Price FROM Products WHERE Price > 50); |
| IN |
TRUE if the operand is equal to one of a list of expressions |
SELECT * FROM Customers
WHERE City IN ('Paris','London'); |
| LIKE |
TRUE if the operand matches a pattern |
SELECT * FROM Customers
WHERE City LIKE 's%'; |
| NOT |
Displays a record if the condition(s) is NOT TRUE |
SELECT * FROM Customers
WHERE City NOT LIKE 's%'; |
| OR |
TRUE if any of the conditions separated by OR is TRUE |
SELECT * FROM Customers
WHERE City = "London" OR Country = "UK"; |
| SOME |
TRUE if any of the subquery values meet the condition |
SELECT * FROM Products
WHERE Price > SOME (SELECT Price FROM Products WHERE Price > 20); |
