AND Condition and OR Condition with Example

AND Condition  and OR  Condition with Example

--====================================================

-- Table Structure and data

--====================================================

select * from XXSD_EMP_TL

EMPNO	ENAME	JOB	MGR	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	800	80	20
7499	ALLEN	SALESMAN	7698	1600	300	30
7521	WARD	SALESMAN	7698	1250	500	30
7566	JONES	MANAGER	7839	2975	1200	20
7654	MARTIN	SALESMAN	7698	1250	1400	30
7698	BLAKE	MANAGER	7839	2850	1100	30
7782	CLARK	MANAGER	7839	2450	900	10
7788	SCOTT	ANALYST	7566	3000	750	20
7839	KING	PRESIDENT		5000	2000	10
7844	TURNER	SALESMAN	7698	1500	300	30
7876	ADAMS	CLERK	7788	1100	220	20
7900	JAMES	CLERK	7698	950	110	30
7902	FORD	ANALYST	7566	3000	700	20
7934	MILLER	CLERK	7782	1300	720	10

select * from XXSD_DEPT_TL

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

--====================================================

-- And Condition Example with Single Table

--====================================================

select * from XXSD_EMP_TL

where JOB = 'SALESMAN'

and SAL > 1000

AND COMM > 100

EMPNO	ENAME	JOB	MGR	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	800	80	20
7499	ALLEN	SALESMAN	7698	1600	300	30
7521	WARD	SALESMAN	7698	1250	500	30
7566	JONES	MANAGER	7839	2975	1200	20
7654	MARTIN	SALESMAN	7698	1250	1400	30
7698	BLAKE	MANAGER	7839	2850	1100	30
7782	CLARK	MANAGER	7839	2450	900	10
7788	SCOTT	ANALYST	7566	3000	750	20
7839	KING	PRESIDENT		5000	2000	10
7844	TURNER	SALESMAN	7698	1500	300	30
7876	ADAMS	CLERK	7788	1100	220	20
7900	JAMES	CLERK	7698	950	110	30
7902	FORD	ANALYST	7566	3000	700	20
7934	MILLER	CLERK	7782	1300	720	10

--====================================================

-- And Condition Example with more than one Table

--====================================================

select * from XXSD_EMP_TL A, XXSD_DEPT_TL B

where A.DEPTNO = B.DEPTNO

AND LOC = 'DALLAS'

AND SAL > 1200

AND COMM < 1000

EMPNO	ENAME	JOB	MGR	SAL	COMM	DEPTNO	DEPTNO_1	DNAME	LOC
7788	SCOTT	ANALYST	7566	3000	750	20	20	RESEARCH	DALLAS
7902	FORD	ANALYST	7566	3000	700	20	20	RESEARCH	DALLAS

--====================================================

-- OR COndition Example

--====================================================

select * from XXSD_EMP_TL

where (JOB = 'SALESMAN' OR DEPTNO = '20' )

Example:-Output as per GREEN rows

EMPNO	ENAME	JOB	MGR	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	800	80	20
7499	ALLEN	SALESMAN	7698	1600	300	30
7521	WARD	SALESMAN	7698	1250	500	30
7566	JONES	MANAGER	7839	2975	1200	20
7654	MARTIN	SALESMAN	7698	1250	1400	30
7698	BLAKE	MANAGER	7839	2850	1100	30
7782	CLARK	MANAGER	7839	2450	900	10
7788	SCOTT	ANALYST	7566	3000	750	20
7839	KING	PRESIDENT		5000	2000	10
7844	TURNER	SALESMAN	7698	1500	300	30
7876	ADAMS	CLERK	7788	1100	220	20
7900	JAMES	CLERK	7698	950	110	30
7902	FORD	ANALYST	7566	3000	700	20
7934	MILLER	CLERK	7782	1300	720	10

select * from XXSD_EMP_TL

where  SAL > 1000

AND JOB = 'SALESMAN' OR DEPTNO = '20'

Example:-Output as per GREEN rows but red colour data is wrong

EMPNO	ENAME	JOB	MGR	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	800	80	20
7499	ALLEN	SALESMAN	7698	1600	300	30
7521	WARD	SALESMAN	7698	1250	500	30
7566	JONES	MANAGER	7839	2975	1200	20
7654	MARTIN	SALESMAN	7698	1250	1400	30
7698	BLAKE	MANAGER	7839	2850	1100	30
7782	CLARK	MANAGER	7839	2450	900	10
7788	SCOTT	ANALYST	7566	3000	750	20
7839	KING	PRESIDENT		5000	2000	10
7844	TURNER	SALESMAN	7698	1500	300	30
7876	ADAMS	CLERK	7788	1100	220	20
7900	JAMES	CLERK	7698	950	110	30
7902	FORD	ANALYST	7566	3000	700	20
7934	MILLER	CLERK	7782	1300	720	10

select * from XXSD_EMP_TL

where  SAL > 1000

AND (JOB = 'SALESMAN' OR DEPTNO = '20')

Example:-Output as per GREEN rows

EMPNO	ENAME	JOB	MGR	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	800	80	20
7499	ALLEN	SALESMAN	7698	1600	300	30
7521	WARD	SALESMAN	7698	1250	500	30
7566	JONES	MANAGER	7839	2975	1200	20
7654	MARTIN	SALESMAN	7698	1250	1400	30
7698	BLAKE	MANAGER	7839	2850	1100	30
7782	CLARK	MANAGER	7839	2450	900	10
7788	SCOTT	ANALYST	7566	3000	750	20
7839	KING	PRESIDENT		5000	2000	10
7844	TURNER	SALESMAN	7698	1500	300	30
7876	ADAMS	CLERK	7788	1100	220	20
7900	JAMES	CLERK	7698	950	110	30
7902	FORD	ANALYST	7566	3000	700	20
7934	MILLER	CLERK	7782	1300	720	10