- Named sub Query in FROM clause is call it as INLINE VIEW.
- Oracle process a inline view faster than sub query
 |
Inline View in Oracle |
--Query to display all the employees who are all getting salary more than their department average salary
SELECT First_name
, last_name
, salary
, department_id
FROM employees a , ( SELECT b.department_id
, AVG(b.salary) avg_sal
FROM employees b
GROUP BY b.department_id
) b
WHERE a.department_id = b.department_id
AND a.salary > b.avg_salary;
Display all the customer who have placed more number of orders by using INLINE view
CREATE TABLE customer_order_det_tb
( custm_id NUMBER ,
custm_nm VARCHAR2(30) ,
order_dt DATE DEFAULT SYSDATE ,
ttl_amt NUMBER(7,2)
);
BEGIN
INSERT INTO customer_order_det_tb VALUES(100, 'Murugappan', '11-Jul-15' , 45000.00);
INSERT INTO customer_order_det_tb VALUES(100, 'Murugappan', '14-Jul-15' , 27000.00);
INSERT INTO customer_order_det_tb VALUES(101, 'Raja' , '13-Jul-15' , 17000.00);
INSERT INTO customer_order_det_tb VALUES(102, 'Ramesh' , '15-Jul-15' , 34000.00);
INSERT INTO customer_order_det_tb VALUES(102, 'Ramesh' , '15-Jul-15' , 38000.00);
COMMIT;
END;
SELECT c.*
FROM customer_order_det_tb c;
--------------------------------------------
custm_id custm_nm order_dt ttl_amt
--------------------------------------------
100 Murugappan 7/11/2015 45000.00
100 Murugappan 7/14/2015 27000.00
101 Raja 7/13/2015 17000.00
102 Ramesh 7/15/2015 34000.00
102 Ramesh 7/15/2015 38000.00
--------------------------------------------
--Writing a Quer to display all the customer who have placed maximum number of order
--Query #1 for inline view
SELECT c.custm_id, c.custm_nm, COUNT(*) total_odr
FROM customer_order_det_tb c
GROUP BY c.custm_id,c.custm_nm
ORDER BY total_odr DESC;
--------------------------------
custm_id custm_nm total_odr
--------------------------------
100 Murugappan 2
102 Ramesh 2
101 Raja 1
--------------------------------
--Query #2 for inline view
SELECT MAX(COUNT(*)) total_odr
FROM customer_order_det_tb cnt
GROUP BY cnt.custm_id, cnt.custm_nm;
----------
total_odr
----------
2
----------
Inline view to find all the customers who have placed maximum number of orders
/****************************************
Syntax
SELECT * FROM <query1> x, <query2> y
WHERE contition;
****************************************/
SELECT query1.* FROM
( SELECT c.custm_id, c.custm_nm, COUNT(*) total_odr
FROM customer_order_det_tb c
GROUP BY c.custm_id,c.custm_nm
ORDER BY total_odr DESC
) query1,
( SELECT MAX(COUNT(*)) total_odr
FROM customer_order_det_tb cnt
GROUP BY cnt.custm_id, cnt.custm_nm
) query2
WHERE query1.total_odr = query2.total_odr;
-------------------------------
custm_id custm_nm total_odr
-------------------------------
100 Murugappan 2
102 Ramesh 2
-------------------------------
DROP TABLE customer_order_det_tb;
--Display name and salary for top three money makers of the company and their designation should not be SALESMAN and PRESIDENT
SELECT first_name, salary
FROM ( SELECT first_name, salary
FROM employees e
WHERE job_id NOT IN ('SALESMAN','PRESIDENT')
ORDER BY salary DESC
)
WHERE ROWNUM < 4;
New Oracle 12c Inline view Syntax
- LATERAL clause for In-line views
- Allows for columns in the inline view to be accessed!!
Example :
--in 11g
SELECT * FROM employees e, (
SELECT *
FROM departments d
WHERE e.department_id = d.department_id);
ORA-0090: "E"."DEPARTMENT_ID": Invalid Identifier
--in 12c
SELECT * FROM employees e, LATERAL(
SELECT *
FROM departments d
WHERE e.department_id = d.department_id
);
--in 11g
WITH t
AS
( SELECT LEVEL a
FROM dual
CONNECT BY LEVEL <= 30)
SELECT *
FROM t, (SELECT *
FROM employees
WHERE department_id = t.a
);
ORA-00904: "T"."A": invalid Identifier
--in 12c
WITH t
AS
(SELECT LEVEL a FROM dual CONNECT BY LEVEL <= 30)
SELECT * FROM t,
LATERAL(SELECT *
FROM employees
WHERE department_id = t.a);
Hope you have enjoyed this little article
Feed backs are always Welcome :) (:
