SQL Examples

Comparing Two Tables

There are several ways to compare the contents of two tables to find the differences between them.

Example: Two tables to be compared are created and sample data is entered.

CREATE TABLE tab1 (

ID INT PRIMARY KEY,

AD VARCHAR (50) );

INSERT INTO tab1 (ID, AD)

VALUES

(1, 'd'),

(2, 'b');

CREATE TABLE tab2 (

ID INT PRIMARY KEY,

AD VARCHAR (50) );

INSERT INTO tab2 (ID, AD)

VALUES

(1 a'),

(2, 'c');

SELECT

ID,

NAME,

'not in tab2' AS note

FROM tab1

EXCEPT

SELECT

ID,

NAME,

'doesn't exist in tab2' AS note

FROM tab2 ;

Deleting Duplicate Rows

Example:

CREATE TABLE dersler(

id SERIAL PRIMARY KEY,

ders VARCHAR(50) NOT NULL);

INSERT INTO dersler(ders) values(‘URETIM PLANLAMA’);

INSERT INTO dersler(ders) values(‘BENZETIM’);

INSERT INTO dersler(ders) values(‘MATEMATİK’);

INSERT INTO dersler(ders) values(‘MATEMATİK’);

INSERT INTO dersler(ders) values(‘BENZETIM’);

INSERT INTO dersler(ders) values(‘YONEYLEM’);

We executed the same insert commands several times and created duplicate lines.

To view duplicate lines.

SELECT

ders,

COUNT( ders )

FROM dersler

GROUP BY ders

HAVING

COUNT( ders )> 1

ORDER BY ders;

To delete duplicate lines;

DELETE FROM dersler d USING dersler b

WHERE

d.id < b.id AND d.ders = b.ders;

In the query, rows with the same ders values but different id values are selected with the where condition.

To delete duplicate rows by creating a new table.

-- Step 1

CREATE TABLE dersler_temp (LIKE dersler);

-- Step 2

INSERT INTO dersler_temp(ders, id)

SELECT

DISTINCT ON (ders) ders,

id

FROM dersler;          

(select * from dersler_temp )

-- Step 3

DROP TABLE dersler;

-- Step 4

ALTER TABLE dersler_temp

RENAME TO dersler ;