Azure SQL optimize IN query

userY1bD 0
Viewed 47

I'm trying to optimize the IN query on Azure SQL. This IN query has more than 50k records. With the optimization, I will use the INNER join. This is what I was trying

SELECT incident_nbr from table_name as incidents INNER JOIN unnest(ARRAY['INC123456', 'INC0123456', 'INC432156']) as inc ON inc=incidents.incident_nbr;

But looks this syntax is incorrect. DBeaver shows this error in the SQL script editor.

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [102] [S0001]: Incorrect syntax near ')'.
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:577)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$1(SQLQueryJob.java:486)

Am I missing anything? TIA.

azure-sql-server azure-sql-database sql-server sql
1 Answers

Is this going to be an sql statement generated on the fly? Try this:

;WITH inc AS (
    SELECT ‘INC0123456’ AS col1
    UNION SELECT ‘INC432156’
)
SELECT incident_nbr 
FROM table_name as incidents 
INNER JOIN inc
ON incidents.incident_nbr = inc.col1;

With 50k rows, you might also want to try inserting into an indexed temp table.

useroqMi0
Related