Question

SQL allows duplicate tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins. Which of the following queries always gives the same answer as the nested query shown below?

select * from R where a in (select S.a from S);

		select R.* from R,S where R.a = S.a;
		select R.* from R, (select distinct a from S) as S1 where R.a = S1.a;
		select R.* from R,S where R.a = S.a and is unique R;
		select distinct R.* from R,S where R.a = S.a;

Answer 1

Here let's undrstand using example i have 2 tables R as customer and S as invoice and both have common field cust_name.

Customer table

Invoice table:

Query 1

SELECT * FROM customer WHERE cust_num IN (SELECT cust_num FROM invoice);

It is concept of sub query. so in this inner query return all cust_num from invoice then customer returns all records which matches to result of subquery . here customer returns all those records which are in invoice.

result:

Query 2

SELECT R.* FROM customer R,invoice S WHERE R.cust_num = S.cust_num;

It is conept of inner join. here inner join of 2 tables customer and invoice so inner join returns all those records wich matches the condition. It retuns duplicate records also.

output:

Query 3:

SELECT R.* FROM customer R, (SELECT DISTINCT cust_num FROM invoice S) AS S1 WHERE R.cust_num = S1.cust_num;

It is concept of both inner join and subquery. in this first sub query is excecuted then result of that as a s1 then after it apply inner join with result of subquery. Here first its select disctinct records from invoice table then inner join with customer base on cust_num.

result:

Query 4:

select R.* from R,S where R.a = S.a and is unique R;

It is wrong syntax. we can not directly write unique on table.

Query 5:

SELECT DISTINCT R .* FROM customer R, invoice S WHERE R.cust_num = S.cust_num;

In this it is concept of inner join same as query 2 but its not return duplicate result because here we specified distinct record from customer so its filter out duplicate records.

result: