ListAGG in SQLSERVER
Starting in SQL Server 2017 the STRING_AGG function is available which simplifies the logic considerably: See SQL Fiddle with Demo In SQL Server you can use FOR XML PATH to get the result: See SQL Fiddle with Demo
sql
SQL “select where not in subquery” returns no results
Update: These articles in my blog describe the differences between the methods in more detail: NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: PostgreSQL NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: Oracle NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL There are three ways to do such a query: LEFT JOIN … Read more
Difference between INNER JOIN and LEFT SEMI JOIN
An INNER JOIN can return data from the columns from both tables, and can duplicate values of records on either side have more than one match. A LEFT SEMI JOIN can only return columns from the left-hand table, and yields one of each record from the left-hand table where there is one or more matches in the right-hand table … Read more
SQL – WHERE (X, Y) IN (A, B)
Why not something simple like the following? WHERE (X = 1 AND Y = 2) OR (X = 5 AND Y = 6) … Or, if you’re looking for rows (based on your example) where Y should be X + 1, then: WHERE Y = X + 1 If you have thousands of OR clauses like the … Read more
Repair all tables in one go
from command line you can use: http://dev.mysql.com/doc/refman/5.0/en/mysqlcheck.html
How to query MongoDB with “like”
That would have to be: Or, similar: You’re looking for something that contains “m” somewhere (SQL’s ‘%‘ operator is equivalent to regular expressions’ ‘.*‘), not something that has “m” anchored to the beginning of the string. Note: MongoDB uses regular expressions which are more powerful than “LIKE” in SQL. With regular expressions you can create any … Read more
SQL: Two select statements in one query
You can do something like this: See, for example: https://dev.mysql.com/doc/refman/5.0/en/union.html
What is the difference between LATERAL JOIN and a subquery in PostgreSQL?
What is a LATERAL join? The feature was introduced with PostgreSQL 9.3. The manual: Subqueries appearing in FROM can be preceded by the key word LATERAL. This allows them to reference columns provided by preceding FROM items. (Without LATERAL, each subquery is evaluated independently and so cannot cross-reference any other FROM item.) Table functions appearing in FROM can also be preceded by the key word LATERAL, but for functions the key … Read more
How do I get the size of a java.sql.ResultSet?
Do a SELECT COUNT(*) FROM … query instead. OR In either of the case, you won’t have to loop over the entire data.
MySQL error: key specification without a key length
The error happens because MySQL can index only the first N chars of a BLOB or TEXT column. So The error mainly happens when there is a field/column type of TEXT or BLOB or those belong to TEXT or BLOB types such as TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT, and LONGTEXT that you try to make a primary key or index. With full BLOB or TEXT without the length value, MySQL is unable to guarantee … Read more