Use Union All & Select Queries Effectively





4.00/5 (3 votes)
Union All,Select
Hi Folks!!!
Iam going to tell you all the magic of using the union all for the data insertion and how do make a select statement as table and perform joins on it
1.Union ALL
--Create table & insert data CREATE TABLE SUPERMAN(NAME VARCHAR(50),AGE INT,SSN INT,SHEIGHT INT,SWEIGHT INT, SNATIVE VARCHAR(50),SHOBBY VARCHAR(50),SJOB VARCHAR(50),SAMBITION VARCHAR(50),SALARY INT) GO INSERT INTO SUPERMAN (NAME,AGE,SSN,SHEIGHT,SWEIGHT,SNATIVE,SHOBBY,SJOB,SAMBITION,SALARY) SELECT 'RAJESH',25,12345,172,75,'TVM','SINGING','SOFTWARE ENGINEER','IAS',500000 UNION ALL SELECT 'MUTHUSAMY RAJESH',22,12346,160,50,'TVM','SINGING','SOFTWARE ENGINEER','IAS',300000 UNION ALL SELECT 'OMPRAKASH',25,12347,172,75,'TVM','DRAWING','SCIENTIST','SR.SCIENTIST',5000000 UNION ALL SELECT 'LAKSHMAN',25,12348,172,75,'TVM','CHATING','SOFTWARE ENGINEER','PROJECT MANAGER',700000 UNION ALL SELECT 'AKSHAY',1,12349,172,75,'CHENNAI','PLAYING WITH PARENTS','SUPERMAN','SUPERMAN',500000 GO --Select & test So you no longer need to use the coventional insert into to every row of insert 2.Use Select query as tables Steps to demonstrate 1.create another 3 tables & insert the data CREATE TABLE NAMES(NAME VARCHAR(50)) GO CREATE TABLE HOBBIES(HNAME VARCHAR(50)) GO CREATE TABLE JOBS(JNAME VARCHAR(50)) insert into names(name) SELECT 'RAJESH' UNION ALL SELECT 'MUTHUSAMY RAJESH' UNION ALL SELECT 'OMPRAKASH' UNION ALL SELECT 'LAKSHMAN' UNION ALL SELECT 'AKSHAY' go insert into hobbies(hname) SELECT 'SINGING' UNION ALL SELECT 'PLAYING WITH PARENTS' UNION ALL SELECT 'DRAWING' UNION ALL SELECT 'CHATING' go insert into jobs(jname) SELECT 'SOFTWARE ENGINEER' UNION ALL SELECT 'SCIENTIST' UNION ALL SELECT 'SUPERMAN' 2.WRITE STORED PROCEDURES TO RETRIEVE THE DETAILS ALTER PROCEDURE SP_TESTER AS BEGIN SELECT A.NAME,A.AGE,A.SSN,A.SHEIGHT,A.SWEIGHT,A.SNATIVE,A.SHOBBY,A.SJOB,A.SAMBITION,A.SALARY,isnull(B.name,'') NCOLS,isnull(C.hname,'') HCOLS,isnull(D.jname,'') JCOLS,E.NAMECOUNT NNAMECOUNT,F.NAMECOUNT HNAMECOUNT,G.NAMECOUNT JNAMECOUNT FROM ((SELECT ROW_NUMBER() OVER (ORDER BY NAME ASC) AS ROWID, * FROM SUPERMAN) A LEFT OUTER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY NAME ASC) AS ROWID, * FROM names) B ON A.ROWID=B.ROWID LEFT OUTER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY hNAME ASC) AS ROWID, * FROM hobbies) C ON A.ROWID=C.ROWID LEFT OUTER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY jNAME ASC) AS ROWID, * FROM jobs) D ON A.ROWID=D.ROWID) LEFT OUTER JOIN (SELECT 1 AS ROWID,COUNT(*) AS NAMECOUNT FROM names) E ON E.ROWID=A.ROWID LEFT OUTER JOIN (SELECT 1 AS ROWID,COUNT(*) AS NAMECOUNT FROM hobbies) F ON F.ROWID=A.ROWID LEFT OUTER JOIN (SELECT 1 AS ROWID,COUNT(*) AS NAMECOUNT FROM jobs) G ON G.ROWID=A.ROWID END Now closely look into the above stored procedure,you could see the alias on each query and joined with the dynamically generated rowid.Finally it gets called as a single table.