Set Operators
In this article let’s understand how to work with multiple tables.
There are 4 SET OPERATORS:
1)UNION Operator
2) Union All
3)Intersect
4)Minus
Note:
MySQL doesn't support Minus and Intersect.
Why do we use these SET Operators?
*These operators are used to join multiple data’s and we can retrieve data from multiple tables with single queries.
*So far we have worked with single tables.Now we will be able to work with multiple tables.
*In order to retrieve the data from multiple tables we use 3 concepts Union/Union All,Joints,sub queries.
Lets see how Union and Union all works:
1)Union operator is used to combine the results of 2 or more SELECT statements.
2)Each SELECT statement within UNION must have the same number of columns.
3)The columns must also have similar data type.
4)The column in each SELECT statement must also be in the same order.
Example: If there are 2 tables Table 1 and Table 2.
*Table 1 has 1 columns with column name NumberId and values as 123,
similarly Table 2 has 1 column with column name as NumId and values as 356.
*Now the union operator will extract the data from 2 tables and will eliminate the duplicates.Hence the output would be 12356.
*Union All gives the content or the data in the tables as it is that is it gives the duplicate data.
Output would be 123356.
*Intersect will give the common data from both the tables.That is 3.
*Minus : T1-T2 will return the date that is present in T1 and not present in T2.
Output will be 12.
Similarly for T2-T1 the output will be 56.
Let’s CREATE 2 tables.
Command:
CREATE TABLE A(SNAME VARCHAR(10),NUM INT(20));
CREATE TABLE B(NUM INT(20),GRADE VARCHAR(30));
INSERT INTO A VALUES(‘ABC’,10);
INSERT INTO A VALUES(‘XYZ’,11);
INSERT INTO A VALUES(‘PQR’,12);
INSERT INTO A VALUES(‘MNO’,14);
COMMIT;
INSERT INTO B VALUES(11,’A’);
INSERT INTO B VALUES(12,’B’);
INSERT INTO B VALUES(13,’C’);
INSERT INTO B VALUES(14,’D’);
COMMIT;
Let’s now perform UNION and Union ALL operations on above created tables.
Command:
SELECT * FROM A;
SLECT * FROM B;
SELECT NUM FROM A UNION SELECT NUM FROM B;//Displays all the records from multiple tables without duplicates.
SELECT NUM FROM A UNION ALL SELECT NUM FROM B;//Displays all the records from multiple tables including duplicates.
That is all about part 7.In the next article let’s see how the keyword ‘Joins’ works.
