Problem with SQL query

Question

I have 2 Project and ProjectList tables like this

Project

ProjectID
Name
ProjectListID - allow null

In projectlist

ProjectListID
ProjName

Now, what I need, I want only those that were selected from the ProjectList table, which ProjectListID is not contained in the Project table. I made a request, but it takes a long time to complete.

select * FROM projectslist pl where pl.ProjectsListID not in (SELECT p.ProjectsListID FROM project p where (p.ProjectsListID is not null and p.ProjectsListID <>0))

Please help me create an optimized query. I am using My SQL.

Answer 1

NOT NULLThe condition in your request is redundant: <> 0implies:

SELECT  *
FROM    projectslist pl
WHERE   pl.ProjectsListID NOT IN 
        (
        SELECT  p.ProjectsListID
        FROM    project p
        WHERE   p.ProjectsListID <> 0
        )

To work quickly, you need to create an index on project (ProjectsListID).

Could you run

EXPLAIN
SELECT  *
FROM    projectslist pl
WHERE   pl.ProjectsListID NOT IN 
        (
        SELECT  p.ProjectsListID
        FROM    project p
        WHERE   p.ProjectsListID <> 0
        )

and bring it here here?

Update:

Since the column in question is NULL, it is best to rewrite the query as NOT EXISTS:

SELECT  *
FROM    projectslist pl
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    project p
        WHERE   p.ProjectsListID = pl.ProjectsListID
                AND p.ProjectsListID <> 0
        )

Answer 2

ProjectList.ProjectListID null ?

LEFT JOINS:

SELECT * FROM ProjectList pl LEFT JOIN Project p ON pl.ProjectListID = p.ProjectListID
WHERE pl.ProjectListID is null

Answer 3

select project_list.*
from project_list left join project using (project_list_id)
where isnull(project.project_id)

This query is much faster than solutions provided by others using a subquery.