/*SIMPLE BUT USEFUL*/
/*CREATING TABLE A1*/
CREATE TABLE A1
(CL1 CHAR)
–DROP TABLE A1
/*INSERTING ELEMENTS TO A1*/
INSERT INTO A1
SELECT ‘A’
UNION ALL
SELECT ‘B’
UNION ALL
SELECT ‘C’
UNION ALL
SELECT ‘D’
UNION ALL
SELECT ‘E’
/*CREATING TABLE A2*/
CREATE TABLE A2
(CL1 CHAR)
–DROP TABLE A2
/*INSERTING ELEMENTS TO A2*/
INSERT INTO A2
SELECT ‘K’
UNION ALL
SELECT ‘L’
UNION ALL
SELECT ‘C’
UNION ALL
SELECT ‘D’
UNION ALL
SELECT ‘E’
/*GETTING ALL ELEMENTS OF A1 WITH CORRESPONDING MATCH IN A2*/
SELECT * FROM A1 A
LEFT OUTER JOIN A2 B
ON(A.CL1=B.CL1)
/*GETTING ELEMENTS ONLY IN A1 TABLE */
SELECT * FROM A1 A
LEFT OUTER JOIN A2 B
ON(A.CL1=B.CL1)
WHERE B.CL1 IS NULL
/*SQL SERVER 2008 SHORTCUT…USAGE OF EXCEPT*/
SELECT * FROM A1
EXCEPT
SELECT * FROM A2
/*SIMPLE BUT USEFUL*/
/*CREATING TABLE A1*/
CREATE TABLE A1
(CL1 CHAR)
–DROP TABLE A1
/*INSERTING ELEMENTS TO A1*/
INSERT INTO A1
SELECT ‘A’
UNION ALL
SELECT ‘B’
UNION ALL
SELECT ‘C’
UNION ALL
SELECT ‘D’
UNION ALL
SELECT ‘E’
/*CREATING TABLE A2*/
CREATE TABLE A2
(CL1 CHAR)
–DROP TABLE A2
/*INSERTING ELEMENTS TO A2*/
INSERT INTO A2
SELECT ‘K’
UNION ALL
SELECT ‘L’
UNION ALL
SELECT ‘C’
UNION ALL
SELECT ‘D’
UNION ALL
SELECT ‘E’
/*GETTING ALL ELEMENTS OF A1 WITH CORRESPONDING MATCH IN A2*/
SELECT * FROM A1 A
LEFT OUTER JOIN A2 B
ON(A.CL1=B.CL1)
/*GETTING ELEMENTS ONLY IN A1 TABLE */
SELECT * FROM A1 A
LEFT OUTER JOIN A2 B
ON(A.CL1=B.CL1)
WHERE B.CL1 IS NULL
/*SQL SERVER 2008 SHORTCUT…USAGE OF EXCEPT*/
SELECT * FROM A1
EXCEPT
SELECT * FROM A2
Advertisement
