GST Billing Software

header image

Conditional SQL

Erachana Line

6. Conditional SQL

Look at the following "Products" table:

P_Id

ProductName

UnitPrice

UnitsInStock

UnitsOnOrder

1

Jarlsberg

10.45

16

15

2

Mascarpone

32.56

23

3

Gorgonzola

15.67

9

20

Suppose that the "UnitsOnOrder" column is optional, and may contain NULL values.

Look at the following SELECT statement:

SELECT ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder)
FROM Products;

In the example above, if any of the "UnitsOnOrder" values are NULL, the result will be NULL.


6.1 If null

The MySQL IFNULL() function lets you return an alternative value if an expression is NULL:

SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL (UnitsOnOrder, 0))
FROM Products


6.2 IS NULL

The SQL Server ISNULL() function lets you return an alternative value when an expression is NULL:

SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL (UnitsOnOrder, 0))
FROM Products


6.3 COALESCE

or we can use the COALESCE () function, like this:

SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE (UnitsOnOrder, 0))
FROM Products


6.4 CASE WHEN

The CASE function lets you evaluate conditions and return a value when the first condition is met (like an IF-THEN-ELSE statement).

Syntax

CASE expression
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
WHEN conditionN THEN resultN
ELSE result
END


6.5 UNION, INTERSECT, CARTISION PRODUCT

The SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

• Each SELECT statement within UNION must have the same number of columns

• The columns must also have similar data types

• The columns in each SELECT statement must also be in the same order

UNION Syntax

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

SQL INTERSECT clause/operator

The SQL INTERSECT clause/operator is used to combine two SELECT statements but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement. This means INTERSECT returns only common rows returned by the two SELECT statements.

Just as with the UNION operator, the same rules apply when using the INTERSECT operator. MySQL does not support the INTERSECT operator.

Syntax

The basic syntax of INTERSECT is as follows.

SELECT column1 [, column2]

FROM table1 [, table2]

[WHERE condition]

INTERSECT

SELECT column1 [, column2]

FROM table1 [, table2]

[WHERE condition]

CARTISION PRODUCT

The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records from two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to either True or where the join-condition is absent from the statement.

Syntax

SELECT table1.column1, table2.column2...

FROM table1, table2 [, table3]