CODEWITHSUNDEEP
sql
Kyle C
Kyle C

Reputation: 227

Do these 2 SQL statements return the same thing?

I have an assignment for class and have the answers to the assignment and it does not match my answer, but to me, it looks like it would return the same thing. I am wondering if this is true.

Here is the Schema:

Here is the question: get the name of those suppliers who do not supply for the project with projid=1.

My code:

select Supplier.sname 
from Supplier
join Supply on Supplier.sid = Supply.sid
join Project on Supply.projid = Project.projid
where not projid = 1;

Professor's code:

Select sname 
from Supplier
Minus
(Select sname 
 from supplier
 Join Supply on Supplier.sid = Supply.sid
 Where projid = '1');

Do these return the same result or not?

Upvotes: 1

Views: 69

Answers (2)

LukStorms
LukStorms

Reputation: 29647

The answer of GSerg already explains it.

But I figured that one way to see if SQL's do what' expected is to run them with sample data.

The MINUS table operator from Oracle DB serves the same purpose as an ANSI SQL standard EXCEPT DISTINCT.

So the test below uses an EXCEPT for the second query.

The first query doesn't return 'Buns Of Moe Bakery' because it ONLY delivers for project 1.

While the second query with the EXCEPT only returns 'Gringotts' because it's the only supplier that doesn't ALSO deliver for project 1.

CREATE TABLE TestSupplier (
 sid int primary key,
 sname varchar(30) not null
);

CREATE TABLE TestProject (
 projid int primary key,
 projname varchar(30) not null
);

CREATE TABLE TestSupply (
 supplyid int primary key,
 sid int,
 projid int,
 supplyname varchar(30) not null,
 foreign key (sid) references TestSupplier(sid),
 foreign key (projid) references TestProject(projid)
);

INSERT INTO TestSupplier (sid, sname) VALUES
(101,'ACME Corp.'),
(102,'Gringotts'),
(103,'Buns Of Moe Bakery');

INSERT INTO TestProject (projid, projname) VALUES
(1,'Unite the World'),
(2,'Gate To Isekai');

INSERT INTO TestSupply (supplyid, sid, projid, supplyname) VALUES
(301,101,1,'Global Brainwash Device'),
(302,101,2,'Industrial Space Food'),
(303,102,2,'Magic Circle'),
(304,103,1,'Donuts');

-- Only rejects suppliers that only deliver supplies for project 1
select distinct Supplier.sname
from TestSupplier as Supplier
join TestSupply as Supply on Supplier.sid = Supply.sid
join TestProject as Project on Supply.projid = Project.projid
where not Project.projid = 1;

| sname      |
| :--------- |
| ACME Corp. |
| Gringotts  |

-- All suppliers without those that also supply to project 1
select distinct sname
from TestSupplier

except

select distinct Supplier.sname
from TestSupplier AS Supplier
join TestSupply AS Supply ON Supply.sid = Supplier.sid
where Supply.projid = 1;

| sname     |
| :-------- |
| Gringotts |

db<>fiddle here

Upvotes: 0

GSerg
GSerg

Reputation: 78155

Your code is wrong.

  • It will show suppliers that supply project 1 provided that they also supply at least one other project. You want to hide such suppliers.
  • It will hide suppliers that do not supply any projects. You want to show such suppliers.
  • It will repeat the suppliers' names for each entry from the Supply table. You want a list of names without repetitions.

The professor's code is correct.

Upvotes: 3

Related Questions