Thursday, 4 February 2016

Combine output of two tables and than filter the final output using &.

Requirement: I have two tables EMP1 and EMP2 having two columns:EMP_ID and EMP_NAME in each table.

Now I will get the combine output of both the tables and than filter the final combine output using &.

Solution: For solution of the above requirement follow the steps as shown below;

Step 1: Create a table EMP1 as shown below:

CREATE TABLE EMP1(EMP_ID NUMBER(3) PRIMARY KEY, EMP_NAME VARCHAR2(20));

Step 2: Insert few records in the EMP1 table using the below INSERT statements:

insert into emp1 values (1,'Susanto');
insert into emp1 values (2,'Moumita');
insert into emp1 values (3,'Subrata');

Step 3: Create a table EMP2 as shown below:

create table emp2 (emp_id number(3) primary key, emp_name varchar2(20));

Step 4: Insert few records in the EMP2 table using the below INSERT statements:

insert into emp2 values (3,'Ajay');
insert into emp2 values (4,'Debabrata');
insert into emp2 values (5,'Sunayna');

Step 5: Now to get the desired output, first we will combine the output of both the tables using UNION clause and then apply the filter using WHERE clause. As we want to pass the bind variable value in WHERE clause at run time we will use &. Thus the query is shown below:

SELECT *
FROM
  (SELECT * FROM emp1
  UNION
  SELECT * FROM emp2
  ) aaa
WHERE emp_id=
  &emp_id;

Thus, when we run the above query it will prompt us to enter the EMP_ID as shown below:

Let us enter the EMP_ID as 5 and click OK. The the output is shown below:

Hence the solution to our requirement.

If you like the post, please comment, share and like me on Facebook.


Thanks & Regards,
Susanto Paul