SQL – Options to Filter values

Spread the love

Filter values in a Query

Use – Not keyword for deselect the values

Filter Keywords Definition
init is alternate of OR condition in SQL , multiple values can be specified at once
isIt is created to Test the NULL values, because it is not possible by operators
Existits use in Sub Queries , If any value exists then query executes the next step, otherwise not
Anyif ANY sub query result meets to given condition , then next query executes , otherwise not
Allif ALL sub query result meets to given condition , then next query executes , otherwise not

Like – with Wild Cards for filter values 

Wild Cards Definition
‘A%’;Start with A alphabet
‘%A’;End with “A” alphabet
‘%A%’;Contains with “A” alphabet
‘_S’;Find value which has second position “S” alphabet – like as , ms , bs
[ ]multiple character input
[BRS]%’;Any value which starts with B or R or S alphabet
[A-F]%’;Any value which starts with (from “A” to “F” ) alphabet
‘[!A-Z]’;Any value which does not start with (from “A” to “F” ) alphabet

BRAND TABLE

S_NOPRODUCTMONTHYEAR
1NIKEDEC2016
2REEBOKJAN2014
3FILAFEB2017
4PUMAJAN2015
5NULLAPR2012

 

Example

IN

SELECT PRODUCT, YEAR, MONTH FROM BRAND
WHERE PRODUCT IN (‘NIKE’ , ‘FILA’)
PRODUCTYEARMONTH
NIKE2016DEC
FILA2017FEB
FILA2015MAR

 

NOT IN

SELECT PRODUCT, YEAR, MONTH FROM BRAND
WHERE PRODUCT NOT IN (‘NIKE’ , ‘FILA’)
PRODUCTYEARMONTH
REEBOK2014JAN
PUMA2015JAN
REEBOK2016FEB

 

 

BETWEEN

SELECT S_NO, PRODUCT,  MONTH, YEAR  FROM BRAND
WHERE YEAR BETWEEN 2014 AND 2016

S_NOPRODUCTMONTHYEAR
1NIKEDEC2016
2REEBOKJAN2014
4PUMAJAN2015

 

 

NOT BETWEEN

SELECT S_NO, PRODUCT,  MONTH, YEAR  FROM BRAND
WHERE YEAR NOT BETWEEN 2014 AND 2016

S_NOPRODUCTMONTHYEAR
3FILAFEB2017
5NULLAPR2012

 

 

IS

SELECT PRODUCT, YEAR, MONTH FROM BRAND
WHERE PRODUCT IS NULL
PRODUCTYEARMONTH
NULL2012APR

IS NOT

SELECT PRODUCT, YEAR, MONTH FROM BRAND
WHERE PRODUCT IS NOT NULL
PRODUCTYEARMONTH
NIKE2016DEC
REEBOK2014JAN
FILA2017FEB
PUMA2015JAN
REEBOK2016FEB
FILA2015MAR

EXIST

SELECT PRODUCT, YEAR, MONTH FROM BRAND
WHERE EXISTS
( SELECT  PRODUCT FROM BRAND WHERE PRODUCT = ‘FILA’)
PRODUCTYEARMONTH
NIKE2016DEC
REEBOK2014JAN
FILA2017FEB
PUMA2015JAN
REEBOK2016FEB
FILA2015MAR
NULL2012APR

NOT EXIST

SELECT PRODUCT, YEAR, MONTH FROM BRAND
WHERE NOT EXISTS
(SELECT  PRODUCT FROM BRAND WHERE PRODUCT = ‘FILA’)
PRODUCTYEARMONTH

ANY

SELECT PRODUCT, YEAR, MONTH FROM BRAND
WHERE YEAR > ANY 
(SELECT  YEAR FROM BRAND WHERE YEAR = 2015)
PRODUCTYEARMONTH
REEBOK2014JAN
NULL2012APR

ALL

SELECT PRODUCT, YEAR, MONTH , QTY FROM BRAND 
WHERE YEAR < ALL 
(SELECT YEAR FROM BRAND WHERE  YEAR = 2015)
PRODUCTYEARMONTH
REEBOK2014JAN
NULL2012APR

LIKE –  START WITH

SELECT PRODUCT, YEAR, MONTH FROM BRAND
WHERE PRODUCT LIKE ‘R%’
PRODUCTYEARMONTH
REEBOK2014JAN
REEBOK2016FEB

LIKE – END WITH

SELECT PRODUCT, YEAR, MONTH FROM BRAND
WHERE PRODUCT LIKE ‘%A’
PRODUCTYEARMONTH
FILA2017FEB
PUMA2015JAN
FILA2015MAR

LIKE – CONTAINS CHARACTER

SELECT PRODUCT, YEAR, MONTH FROM BRAND
WHERE PRODUCT LIKE ‘%K%’
PRODUCTYEARMONTH
NIKE2016DEC
REEBOK2014JAN
REEBOK
2016FEB

LIKE – SEACH BASIS ON SECOND PLACE CHARECTER

SELECT PRODUCT, YEAR, MONTH FROM BRAND
WHERE PRODUCT LIKE ‘%_K%’
PRODUCTYEARMONTH
NIKE2016DEC
REEBOK2014JAN
REEBOK2016FEB

LIKE – START WITH CHARECTERS (Multiple character input)

SELECT PRODUCT, YEAR, MONTH FROM BRAND
WHERE PRODUCT LIKE ‘[FN]%’
PRODUCT
YEARMONTH
NIKE2016DEC
FILA2017FEB
FILA2015MAR

LIKE – BETWEEN THE RANGE OF APLPHBET

SELECT PRODUCT, YEAR, MONTH FROM BRAND
WHERE PRODUCT LIKE ‘[P-R]%’
PRODUCTYEARMONTH
REEBOK2014JAN
PUMA2015JAN
REEBOK2016FEB

LIKE – NOT BETWEEN THE RANGE OF APLPHBET

SELECT PRODUCT, YEAR, MONTH FROM BRAND
WHERE PRODUCT NOT LIKE ‘[P-R]%’
PRODUCTYEARMONTH
NIKE2016DEC
FILA2017FEB
FILA2015MAR

 

 

 

 

sql like, in , between , contains , start with , end with , any , all keyword search in sql values
like, in , between , contains , start with , end with , any , all keyword search in sql values
like, in , between , contains , start with , end with , any , all keyword search in sql values into table sql
like, in , between , contains , start with , end with , any , all keyword search in sql values into table
like, in , between , contains , start with , end with , any , all keyword search in sql values into a table
like, in , between , contains , start with , end with , any , all keyword search in sql records into a table
like, in , between , contains , start with , end with , any , all keyword search in sql values command sql
select like, in , between , contains , start with , end with , any , all keyword search in sql valuesby sql query
sql query find like, in , between , contains , start with , end with , any , all keyword search in sql values from table
sql like, in , between , contains , start with , end with , any , all keyword search in sql values into table
sql like, in , between , contains , start with , end with , any , all keyword search in sql values into a table
sql like, in , between , contains , start with , end with , any , all keyword search in sql records into a table
sql like, in , between , contains , start with , end with , any , all keyword search in sql data into a table
sql like, in , between , contains , start with , end with , any , all keyword search in sql data into table
sql like, in , between , contains , start with , end with , any , all keyword search in sql data into existing table