21 votes

Find IDs from a list that don't exist in a table

One way of doing it would be to use VALUES to create a table expression with the ids to check and EXCEPT to find the missing ones. SELECT id FROM (VALUES(4),(5),(6)) V(id) EXCEPT SELECT id FROM ...
Martin Smith's user avatar
  • 84.6k
17 votes

Find IDs from a list that don't exist in a table

While using EXCEPT like @Martin provided, remember to make it EXCEPTALL, unless you want to pay a little extra for trying to fold duplicates. BTW, a VALUES expression can stand on its own: VALUES (4),(...
Erwin Brandstetter's user avatar
11 votes

MySQL: Create index If not exists

If you name the index, the query will fail if the index already exists (tested in MySQL 8.0): ALTER TABLE `my_table` ADD INDEX `col_idx` (`col` DESC); Error Code: 1061. Duplicate key name 'col_idx'; ...
the_nuts's user avatar
  • 362
7 votes

MySQL: Create index If not exists

SELECT COUNT(*) FROM information_schema.statistics WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'table_name' AND INDEX_NAME = 'index_name'; My query would give you the count of indexes ...
Bennet Joseph's user avatar
6 votes
Accepted

select data from table which dont have row in another table

What you want is called "anti-join" (or "anti-semijoin"). There are 3 main ways to do this kind of query in MySQL (and some more in other DBMS, that have implemented EXCEPT operator): NOT IN subquery ...
ypercubeᵀᴹ's user avatar
5 votes
Accepted

Check table before delete a constraint

You can use information_schema to check if the table exists but in MySQL there is a simpler method: DROP TABLE IF EXISTS employees ;
ypercubeᵀᴹ's user avatar
5 votes
Accepted

Problem with IF NOT EXISTS query in ms sql

If you want to script conditional addition of a column (only if it does not already exist in the table), you can use the sys.columns system catalogue in this way: IF NOT EXISTS ( SELECT * FROM ...
Andriy M's user avatar
  • 23k
3 votes
Accepted

Help filtering out records in MYSQL

SELECT koalaName FROM main GROUP BY koalaName HAVING SUM( fate IN ('Released', 'Relocated', 'Care') ) > 0 AND SUM( fate = 'Found Dead') = 0
Akina's user avatar
  • 19.8k
3 votes
Accepted

Optimization: insert only not existing rows with additional conditions

Besides adding indexes (which you say is not allowed), the query is unnecessarily complex. The 2 of the 3 table references in the correlated subquery are not needed as they join to the same tables in ...
ypercubeᵀᴹ's user avatar
3 votes
Accepted

How do I identify all the students who have passed?

The problem you describe is known as relational division. There are basically two ways to approach it: Transform for all x : p(x) to not exists x : not p(x) In other words, transform students who ...
Lennart - Slava Ukraini's user avatar
3 votes
Accepted

Combine WHERE NOT EXISTS with ON DUPLICATE KEY UPDATE

The problem in your query is this part: ON DUPLICATE KEY UPDATE -- paramvalue = t.extension ; The alias t is not visible in that part of the query. However, assuming that the two values, ...
ypercubeᵀᴹ's user avatar
2 votes

Find IDs from a list that don't exist in a table

Just use a second table and join them. create table images1( id int not null ); create table images2( id int not null ); insert into images1 values(1), (2), (3), (4), (6), (8); insert into ...
dfundako's user avatar
  • 392
2 votes
Accepted

select columns if a column has a value

There are a couple of options here. One is to repeat the given structure for each column: select if(EmpNum is null,"",ID) as s_id, if(EmpNum is null,"",Name) as s_name, .. etc from users where ...
Michael Green's user avatar
2 votes

How to select rows based on other tables - MySql

Is this what you're looking for? SELECT Whatever FROM UserTable WHERE UserTable.username NOT IN ( SELECT CheckTable.user_id FROM CheckTable ); If this is not suitable, please expand. Possibly ...
Vérace's user avatar
  • 29.8k
2 votes
Accepted

Duplicate rows with a few changes in a table with a composite primary key, but only if target does not exist

Taking what your query says literally, you don't have to worry about what exists in xxx.myTable - because your statement will create it, therefore it won't have anything in it (or, if it already ...
RDFozz's user avatar
  • 11.6k
2 votes
Accepted

No data inserted with this WHERE NOT EXISTS expression?

Your condition compares the column value to itself: WHERE NOT EXISTS(SELECT code FROM subject WHERE code = code) Boils down to: WHERE NOT EXISTS(SELECT FROM subject WHERE code IS NOT NULL) IOW, it'...
Erwin Brandstetter's user avatar
2 votes

Get slave status using sql query inside if-else condition

Why not simply do STOP SLAVE? If not "slave_running", it will have no effect.
Rick James's user avatar
  • 77.9k
1 vote
Accepted

INSERT-ing to a table if the the data does not already exits - how do indexes peform in this scenario?

A clustered index IS the table - so if the rows you are adding are already ordered there is not much work to do. A non-clustered index is an extra structure that you will have to maintain - so Inserts,...
Stephen Morris - Mo64's user avatar
1 vote
Accepted

Check if table exists on a linked server

You can query the Information_Schema views on the linked server: if exists( select * from [Linked_Server_Name].[Database_Name].INFORMATION_SCHEMA.TABLES where table_name ...
MJH's user avatar
  • 166
1 vote
Accepted

Surcharges that were not invoiced

Will this work for you? Assuming Invoices has an ID column which you can replace in this code: SELECT * FROM Invoices WHERE InvoiceID NOT IN (SELECT InvoiceID FROM Invoices WHERE chargecode = '...
Jacob H's user avatar
  • 710
1 vote

select string that doesn't contain a substring/field

I believe the problem could be summarised like this: Select a row from TableA if no row exists in TableB such that TableB.keyword is a substring of TableA.content. It would probably be fair to say ...
Andriy M's user avatar
  • 23k
1 vote

How do I identify all the students who have passed?

How about this: USE tempdb CREATE TABLE dbo.Student ( s_idn int, name varchar(30) ); CREATE TABLE dbo.Exam ( e_idn int IDENTITY, s_idn int, pass char(1), active char(1) ) GO INSERT INTO ...
jyao's user avatar
  • 3,033
1 vote

MS Access: adding column if not exist (or try/catch it)

tableexist = CheckTableExistance("Tbl_Name"); if (tableexist == true) { using (System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select * from Tbl_Name&...
Deepak's user avatar
  • 21

Only top scored, non community-wiki answers of a minimum length are eligible