query without using subquery or explicit join

Question

I need to re-write this without using subquery and explicit join..help please been looking around for a while

SELECT snum, pnum, shipdate
FROM supply as b
WHERE EXISTS (SELECT pname, pnum FROM parts as a WHERE b.pnum = a.pnum);

Answer 1

I believe you've been given a trick question. The answer is this.

SELECT snum, pnum, shipdate
FROM supply

The reason is that the condition you're checking for should be impossible in a well designed database.

Let's have a look at what the original query is doing.

SELECT snum, pnum, shipdate
FROM supply as b
WHERE EXISTS (
    SELECT pname, pnum FROM parts as a WHERE b.pnum = a.pnum
);

It's getting every row in supply where there's a corresponding part in parts. How do you do this in a query without a join? You shouldn't have to do it in the first place. Instead you should rely on referential integrity.

Referential integrity is a property of good table design that says all references are valid. There should be no need to check that each part in supply exists in parts because such a condition should be impossible. You do this with a well designed schema with appropriate use of foreign key and not null constraints.

(My examples are done in Postgres. The syntax for your database may vary.)

create table parts(
    pnum integer primary key,
    pname text not null
);

create table supply(
    snum integer primary key,
    pnum integer references parts(pnum) not null,
    shipdate date not null
);

By declaring supply.pnum as references parts(pnum) we have told the database this is a foreign key and there must be a corresponding row in parts. Adding not null guarantees each row in supply must supply a valid part. The database enforces these constraints automatically.

(Note that MySQL takes a little more convincing to enforce a foreign key constraint. Because MySQL is so non-standard one can pick up bad habits learning on it. Use Postgres or even SQLite instead.)

You can also add the constraints to an existing table using alter table.

test=> alter table supply alter pnum set not null;
ALTER TABLE
test=> alter table supply add constraint pnum_fk foreign key (pnum) references parts(pnum);
ALTER TABLE

For example, let's say we have these parts.

test=> select * from parts;
 pnum |  pname  
------+---------
    1 | flange
    2 | thingy
    3 | whatsit

We can insert a row into supply for one of those parts.

test=> insert into supply (pnum, shipdate) values (3, '2018-02-03');
INSERT 0 1

But if we try to insert a part that doesn't exist, we get an error.

test=> insert into supply (pnum, shipdate) values (99, '2018-02-03');
ERROR:  insert or update on table "supply" violates foreign key constraint "supply_pnum_fkey"
DETAIL:  Key (pnum)=(99) is not present in table "parts".

Or one with a null part number...

test=> insert into supply (pnum, shipdate) values (null, '2018-02-03');
ERROR:  null value in column "pnum" violates not-null constraint
DETAIL:  Failing row contains (1, null, 2018-02-03).

The condition you're testing for is now impossible. There's no need for it. So the answer is:

SELECT snum, pnum, shipdate
FROM supply

Answer 2

One way is INTERSECT(column list is limited to common ones):

SELECT pnum
FROM supply
INTERSECT
SELECT pnum
FROM parts;

---

Using SEMIJOIN:

SELECT b.snum, b.pnum, b.shipdate
FROM supply as b
SEMIJOIN parts as a
   ON b.pnum = a.pnum;

Answer 3

The subquery can be replaced by an INNER JOIN, as follows :

SELECT b.snum, b.pnum, b.shipdate
FROM 
    supply as b
    INNER JOIN parts as a ON b.pnum = a.pnum
GROUP BY b.snum, b.pnum, b.shipdate

You could also go for implicit join, but I would not recommend it as it is less readable and out of favor as of now :

SELECT b.snum, b.pnum, b.shipdate
FROM 
    supply as b,
    parts as a
WHERE b.pnum = a.pnum
GROUP BY b.snum, b.pnum, b.shipdate