MySQL Select X from a Where Not in ( Select X from B ) - Unexpected Result

MySQL SELECT x FROM a WHERE NOT IN ( SELECT x FROM b ) - Unexpected result

From documentation:

To comply with the SQL standard, IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.

This is exactly your case.

Both IN and NOT IN return NULL which is not an acceptable condition for WHERE clause.

Rewrite your query as follows:

SELECT  *
FROM    match m
WHERE   NOT EXISTS
        (
        SELECT  1
        FROM    email e
        WHERE   e.id = m.id
        )

Select query to last row of table giving wrong result in mysql

That's most likely because your pro_id column is defined as text.
You can tell sql to order by the numeric value in that column with

SELECT * FROM product ORDER BY CONVERT(pro_id, UNSIGNED INTEGER) DESC LIMIT 1

Why not equal displaying actual result in select queries by joining two select tables

Use a HAVING clause with the condition that the min transaction_date is '2022-06-28':

SELECT scd.student_id, 
       MIN(sc.transaction_date) transaction_date
FROM student_collection_details scd INNER JOIN student_collection sc
ON scd.student_collection_id = sc.id
WHERE sc.transaction_date BETWEEN '2022-06-01 00:00:00' AND '2022-06-28 00:00:00'
  AND scd.admission_year_id = 2
  AND scd.month_id = 21
  AND scd.collection_head_id = 9
GROUP BY scd.student_id
HAVING MIN(sc.transaction_date) = '2022-06-28 00:00:00';

---

Related Topics