Force MySQL to return duplicates from WHERE IN clause without using JOIN/UNION?

by

I’m not sure why you want to ban JOIN as its fairly essential to SQL. It’s like banning function calls in a functional language.

A good way to solve this is to create a result set containing the ids you want to return and join with it. Here’s one way to do it:

SELECT Table1.*
FROM Table1
JOIN (SELECT 1 AS id
      UNION ALL SELECT 2
      UNION ALL SELECT 3
      UNION ALL SELECT 4
      UNION ALL SELECT 5
      UNION ALL SELECT 1
      UNION ALL SELECT 2
      UNION ALL SELECT 5
      UNION ALL SELECT 5) AS T1
ON Table1.id = T1.id

I’m not sure if you have considered this method? It has none of the problems that you seem to be afraid of.

If you ban joins you can’t do this unless you use a stored procedure, which I’d say is worse than joining.

More Related Contents:

  1. Remove duplicate rows in MySQL
  2. Delete all Duplicate Rows except for One in MySQL? [duplicate]
  3. Find duplicate records in MySQL
  4. How to delete duplicates on a MySQL table?
  5. How to abort INSERT operation in MySql trigger?
  6. Prevent auto increment on MySQL duplicate insert
  7. Deleting duplicate rows from a table
  8. MySQL – SELECT WHERE field IN (subquery) – Extremely slow why?
  9. In MySQL, can I copy one row to insert into the same table?
  10. MySQL multiple columns in IN clause
  11. Removing duplicates with unique index
  12. How to delete duplicate rows from a MySQL table
  13. How do I delete all the duplicate records in a MySQL table without temp tables
  14. MySQL delete duplicate records but keep latest
  15. How to remove duplicate entries from a mysql db?
  16. Count duplicates records in Mysql table?
  17. How to do unique constraint works with NULL value in MySQL
  18. MySQL update table based on another tables value
  19. On Duplicate Key Update same as insert
  20. How to export and import a .sql file from command line with options? [duplicate]
  21. a count for each join – optimisation
  22. The equivalent of SQLServer function SCOPE_IDENTITY() in mySQL?
  23. How to add not null constraint to existing column in MySQL
  24. MySQL Aggregate Functions without GROUP BY clause
  25. How to insert values in table with foreign key using MySQL?
  26. Is there a way to “limit” the result with ELOQUENT ORM of Laravel?
  27. Fixing “Lock wait timeout exceeded; try restarting transaction” for a ‘stuck” Mysql table?
  28. the MySQL service on local computer started and then stopped
  29. Rename a table in MySQL
  30. Optimistic locking in MySQL

Leave a Comment