SELECT FROM EMPDEPT 14 4 56 Rows Are Generated SELECT

Select from empdept 14 4 56 rows are generated select

This preview shows page 22 - 25 out of 31 pages.

SELECT EMPNO,ENAME,JOB,SAL,DNAME,LOC FROM EMP,DEPT (14 * 4 = 56 Rows Are Generated) SELECT EMPNO,ENAME,JOB,SAL,DNAME,LOC,GRADE FROM EMP,DEPT,SALGRADE (14 * 4 * 5 = 280 Rows Are Generated) 4. SELF JOIN: This join concept will support to join data from a single table which is used for more than once but with different alias names, where joining condition and data retrieval is done on a single table. Display empno,ename,mgr and mgr name of all the employees? SELECT M.EMPNO,M.ENAME,M.MGR,E.ENAME FROM EMP E,EMP M WHERE E.EMPNO=M.MGR
Image of page 22
ANSI Syntaxes: These joins will support to join the columns from 2 or more tables by using keywords, which are placed at FROM clause of SELECT statement. It supports to implement the following types of joins. 1. INNER JOIN or JOIN 2. CROSS JOIN 3. outer joins 3.1. LEFT OUTER JOIN 3.2. RIGHT OUTER JOIN 3.3. FULL OUTER JOIN Syntax: SELECT ..................................................... FROM table1 [table1_alias] join_type table2 [table2_alias] ON (joining condition) join_type table3 [table3_alias] ON (joining condition) ............. [WHERE condition(s)] .............................. INNER JOIN: SELECT EMPNO,ENAME,JOB,DNAME,LOC FROM EMP E INNER JOIN DEPT D ON(E.DEPTNO=D.DEPTNO) SELECT EMPNO,ENAME,JOB,SAL,GRADE FROM EMP E INNER JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL) SELECT EMPNO,ENAME,JOB,SAL,DNAME,GRADE FROM EMP E INNER JOIN DEPT D ON(E.DEPTNO=D.DEPTNO) INNER JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL) SELECT EMPNO,ENAME,JOB,SAL,DNAME,GRADE FROM EMP E JOIN DEPT D ON(E.DEPTNO=D.DEPTNO) JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL) SELECT M.EMPNO,M.ENAME,M.MGR,E.ENAME FROM EMP E INNER JOIN EMP M ON (E.EMPNO=M.MGR) CROSS JOIN: It is similar to cartesian join. SELECT EMPNO,ENAME,JOB,DNAME,LOC FROM EMP CROSS JOIN DEPT OUTER JOINS: 1. LEFT OUTER JOIN: SELECT SELECT EMPNO,ENAME,JOB,E.DEPTNO,DNAME,LOC FROM EMP E LEFT OUTER JOIN DEPT D ON (E.DEPTNO=D.DEPTNO) 2. RIGHT OUTER JOIN: SELECT EMPNO,ENAME,JOB,E.DEPTNO,DNAME,LOC FROM EMP E RIGHT OUTER JOIN DEPT D ON (E.DEPTNO=D.DEPTNO) 3. FULL OUTER JOIN : It is a combination of left and right outer joins. SELECT EMPNO,ENAME,JOB,E.DEPTNO,DNAME,LOC FROM EMP E FULL OUTER JOIN DEPT D ON (E.DEPTNO=D.DEPTNO)
Image of page 23
NESTED QUERIES A query which allows to write the other query refers to Nested Query. Nested Query always gets executed based on the following way; "First Executes Inner Query and then Executes Outer Query". A query which allows to write the other query it refers to "Outer or Main Query" whereas a query which is written in the other query it is called "Inner Query or Sub Query" SQL Server supports to write 32 Sub Queries. SQL Server does not support to return multiple columns. Classification Of Nested Queries: Sub Query | --------------------------------------------------------- | | Based On Usage Based On Execution Outer Query Inner Query 1. Normal Sub Query 1. Single Column - Single Row 2. Correlated Sub Query 2. Single Column - Multiple Rows A Sub query can be written; 1. at WHERE clause 2. at HAVING clause 3. at FROM clause (INLINE VIEW) 4. as an Expression (SCALAR SUB QUERY) 5. under INSERT,UPDATE,DELETE queries.
Image of page 24
Image of page 25

You've reached the end of your free preview.

Want to read all 31 pages?

  • Fall '16
  • henry chag
  • Microsoft SQL Server, emp, Join

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture