In database management, selecting rows from one table that does not have matching entries in another table means returning the rows that are present in one table but do not have the same entry in any other table. This scenario often arises in various data validation and analysis processes.
In this article, We will learn about How to select rows with no matching entry in another table in SQLite by understanding various methods along with the examples and so on.
How to Select Rows with No Matching Entry in Another Table in SQLite?
When working with databases, it’s often necessary to query data from one table that has no corresponding entry in another table. In SQLite, this can be achieved using a combination of various methods. Let’s see some methods that help us to Select Rows with No Matching Entries in Another Table in SQLite as follows.
- Using LEFT JOIN with IS NULL
- Utilizing Subqueries
- Using NOT EXISTS
To understand How to select rows with no matching entry in another table in SQLite we need a table on which we will perform various operations and queries. Here we will consider a table called ORDERS and CUSTOMERS where the ORDERS table contains ORDERID, ORDERDATE. Here the CUSTOMERS table contains CUSTOMERSID, and CUSTOMERSNAME as Columns. Here the CUSTOMERSID which is a PRIMARY KEY acts as a Foreign Key for ORDERs TABLE.
Orders table
CUSTOMERS TABLE:
Customer Table
These two tables “ORDERS” and “CUSTOMERS” are taken as an example for explaining the concept of select rows with no matching entry in another table.
1. Using LEFT JOIN with IS NULL
- The LEFT JOIN query technique is utilized to merge data from the “Customers” and “Orders” tables based on their shared column “CustomerID“.
- This method ensures that every row from the “Customers” table is included in the output, regardless of whether there are corresponding entries in the “Orders” table. To refine the results, the WHERE clause is used, filtering out entries where the corresponding row in the “Orders” table does not exist, using the condition “Orders”.
- In instances where no matching items are found in the “Orders” table, the query retrieves the CustomerID and CustomerName from the “Customers” table. This approach facilitates thorough analysis, capturing both customers who have placed orders and those who have not, while providing essential customer information.
SELECT Customers.CustomerID, Customers.CustomerName
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.CustomerID IS NULL;
Output:
Output of the row that is present in customers table but not in orders table
From the two table given above, there is only one row (i.e. row with CUSTOMERID=3) with no matching entry in another table.
2. Utilizing Subqueries
- In order to refine entries from the “Customers” table based on criteria derived from the “Orders” table, this query using a subquery within the WHERE clause.
- The subquery (SELECT DISTINCT CustomerID FROM Orders) generates a unique list of CustomerIDs present in the “Orders” database. Subsequently, the primary query selects rows from the “Customers” table where the CustomerID is not present in the list obtained from the subquery.
- When a CustomerID does not appear in the unique list of CustomerIDs from the Orders table, the query retrieves the CustomerID and CustomerName from the Customers table.
- This method enables the extraction of specific customer data from the Customers table based on their absence or presence in the Orders table, facilitating targeted analysis and reporting.
SELECT CustomerID, CustomerName
FROM Customers
WHERE CustomerID NOT IN (SELECT DISTINCT CustomerID FROM Orders);
Output:
Output of the row that is present in customers table but not in orders table
From the two table given above, there is only one row (i.e. row with CUSTOMERID=3) with no matching entry in another table.
3. Using NOT EXISTS
- Using a correlated subquery within the WHERE clause, this query operates similarly to the subquery approach, aiming to ascertain whether the “Orders” table contains any matching items.
- For each row in the “Customers” database, the NOT EXISTS clause is utilized to evaluate if the subquery yields any results. The subquery, SELECT 1 FROM Orders WHERE CustomerID = Orders.CustomerID, is correlated with the outer query, checking for matched entries based on CustomerID.
- Utilizing the NOT EXISTS clause, the query extracts CustomerID and CustomerName from the Customers table in instances where the Orders table lacks a corresponding record.
- This methodology allows for the selection of specific customer data based on the absence of associated entries in the Orders table, facilitating targeted analysis and reporting.
SELECT CustomerID, CustomerName
FROM Customers
WHERE NOT EXISTS (SELECT 1 FROM Orders WHERE Customers.CustomerID = Orders.CustomerID);
Output:
Output of the row that is present in customers table but not in orders table
From the two table given above, there is only one row (i.e. row with CUSTOMERID=3) with no matching entry in another table.
Conclusion
Overall, selecting rows with no matching entry in another table is a common task in database querying, essential for various data analysis and validation processes. By mastering techniques such as LEFT JOIN, subqueries, or NOT IN/NOT EXISTS, you can efficiently handle such scenarios in SQLite databases.