Can this be written in a better way?

Question

I have these two tables:

CREATE TABLE x
(
    id INT NOT NULL,
    exclude BIT NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE y
(
    id INT NOT NULL,
    field1 INT NOT NULL,
    field2 INT NOT NULL,
    field3 INT NOT NULL,
    field4 INT NOT NULL,
    field5 INT NOT NULL,
    PRIMARY KEY (id)
);

And this sproc:

CREATE PROCEDURE proc1
(
    i1 INT,
    i2 INT,
    i3 INT,
    i4 INT,
    i5 INT
)
BEGIN

SELECT
    id
FROM
    y
WHERE
    field1 = i1,
    field2 = i2,
    field3 = i3,
    field4 = i4,
    field5 = i5
    AND id NOT IN (
        SELECT
            y.id
        FROM
            y, x
        WHERE
            y.field1 = i1
            AND y.field2 = i2,
            AND y.field3 = i3,
            AND y.field4 = i4,
            AND y.field5 = i5
            AND x.id = y.id
            AND x.exclude = 1);

END IF;

I basically want to get the id field from y except those that exist in x with exclude = 1, can this be done with joins or written in a better way?

Many thanks

Note: The reason a join is used in the 'NOT IN' subquery is for performance sake, table x contains a lot of records while the select statement on table y returns only a couple of records

Edit: Also please note that the id in y might or might not exist in x (otherwise a simple join will do), I want all the ids in y except those that exist in x with exclude = 1

Answer 1

I would ask why this query does not return the correct results.

    SELECT
        y.id
    FROM
        y, x
    WHERE
        y.field1 = i1
        AND y.field2 = i2,
        AND y.field3 = i3,
        AND y.field4 = i4,
        AND y.field5 = i5
        AND x.id = y.id
        AND x.exclude = 0;

If you are having performance issues, you may want to look at adding an index (table y foreign key to x.id).

Answer 2

I'm not sure I follow your logic for using a join inside your subquery. You're doing the same work twice.

You could try:

SELECT id
FROM y
WHERE field1 = 2
AND field2 = 3
AND field3 = 4
AND field4 = 5
AND field5 = 6
AND NOT EXISTS(
SELECT x.id
FROM x
WHERE x.id = y.id
AND x.exclude = 1
)