VARCHAR is variable-length. CHAR is fixed length. If your content is a fixed size, you’ll get better performance with CHAR. See the MySQL page on CHAR and VARCHAR Types for a detailed explanation (be sure to also read the comments).
They’re used in different places. GROUP BY modifies the entire query, like: select customerId, count(*) as orderCount from Orders group by customerId But PARTITION BY just works on a window function, like ROW_NUMBER(): select row_number() over (partition by customerId order by orderId) as OrderNumberForThisCustomer from Orders GROUP BY normally reduces the number of rows returned … Read more
The problem is that MySQL, for whatever inane reason, doesn’t allow you to write queries like this: UPDATE myTable SET myTable.A = ( SELECT B FROM myTable INNER JOIN … ) That is, if you’re doing an UPDATE/INSERT/DELETE on a table, you can’t reference that table in an inner query (you can however reference a … Read more
Escape the apostrophe (i.e. double-up the single quote character) in your SQL: INSERT INTO Person (First, Last) VALUES (‘Joe’, ‘O”Brien’) /\ right here The same applies to SELECT queries: SELECT First, Last FROM Person WHERE Last=”O”‘Brien’ The apostrophe, or single quote, is a special character in SQL that specifies the beginning and end of string … Read more
In order to avoid a “Division by zero” error we have programmed it like this: Select Case when divisor=0 then null Else dividend / divisor End ,,, But here is a much nicer way of doing it: Select dividend / NULLIF(divisor, 0) … Now the only problem is to remember the NullIf bit, if I … Read more
SELECT … INTO … only works if the table specified in the INTO clause does not exist – otherwise, you have to use: INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key This assumes there’s only two columns in dbo.TABLETWO – you need to specify the columns otherwise: INSERT INTO dbo.TABLETWO (col1, … Read more
Yes, that’s possible – you can use INSERT … ON DUPLICATE KEY UPDATE. Using your example: INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12) ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);
Fast ways Given your specifications (plus additional info in the comments), You have a numeric ID column (integer numbers) with only few (or moderately few) gaps. Obviously no or few write operations. Your ID column has to be indexed! A primary key serves nicely. The query below does not need a sequential scan of the … Read more
Here’s a simple query: SELECT t1.ID FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.ID = t2.ID WHERE t2.ID IS NULL The key points are: LEFT JOIN is used; this will return ALL rows from Table1, regardless of whether or not there is a matching row in Table2. The WHERE t2.ID IS NULL clause; this … Read more
PostgreSQL 9.0 or later: Modern Postgres (since 2010) has the string_agg(expression, delimiter) function which will do exactly what the asker was looking for: SELECT company_id, string_agg(employee, ‘, ‘) FROM mytable GROUP BY company_id; Postgres 9 also added the ability to specify an ORDER BY clause in any aggregate expression; otherwise you have to order all … Read more