Hot answers tagged

47

If you SELECT COUNT(*) FROM TABLE that only returns one row (the count), is relatively light, and is the way to get that datum. And SELECT * is not a physical no-no, in that it is legal and allowed. However, the problem with SELECT * is that you can cause a lot more data movement. You operate on every column in the table. If your SELECT only includes a ...


35

My first thought would be to use the INFORMATION_SCHEMA first, so you get to know (in one query for all tables in the MySQL instance) which tables have an active column and then use that info to construct your queries. And this is probably the most sane approach. There is one other, tricky way though that works no matter if the table has or not such a ...


31

The phrase ORDER BY 1 refers to different columns; in the first it will be id, in the second val. Since id is the key it will be indexed and the order by will be a trivial amount of work. To order by val, however, the system will have to retrieve every row, sort the complete table by val, then choose just one of those rows. Change both queries to order by ...


27

mysqldump has the --where option to execute a WHERE clause for a given table. Although it is not possible to mysqldump a join query, you can export specific rows from each table so that every row fetched from each table will be involved in the join later on. For your given query, you would need to mysqldump three times: First, mysqldump all table3 rows ...


27

Strictly, yes, the FROM clause of a SELECT statement is not optional. The syntax for SQL-99 details the basic SELECT statment, and the FROM clause doesn't have any square brackets around it. That indicates the standard considers it non-optional: SELECT [ DISTINCT | ALL ] {Column expression [ AS name ]} [ ,... ] | * FROM <Table reference> [ ...


26

SELECT coalesce(MAX(post_id),0) AS max_id FROM my_table WHERE org_id = 3 or SELECT case count(*) when 0 then 0 else MAX(post_id) end AS max_id FROM my_table WHERE org_id = 3; if you want max(post_id) to be null when there is 1 row but post_id is null


21

Reposting my answer to a similar question regarding SQL Server: In the SQL world, order is not an inherent property of a set of data. Thus, you get no guarantees from your RDBMS that your data will come back in a certain order -- or even in a consistent order -- unless you query your data with an ORDER BY clause. So, to answer your question, ...


20

List all users who have been assigned a particular role -- Change 'DBA' to the required role select * from dba_role_privs where granted_role = 'DBA' List all roles given to a user -- Change 'PHIL@ to the required user select * from dba_role_privs where grantee = 'PHIL'; List all privileges given to a user select lpad(' ', 2*level) || granted_role ...


20

They are called quoted identifiers and they tell the parser to handle the text between them as a literal string. They are useful for when you have a column or table that contains a keyword or space. For instance the following would not work: CREATE TABLE my table (id INT); But the following would: CREATE TABLE `my table` (id INT); Also, the following ...


20

In addition to the answer already provider, I feel that it's worth pointing out that developers are often too lazy when working with modern ORM's such as Entity Framework. Whilst DBA's try their hardest to avoid SELECT *, developers often write the semantically equivalent eg, in c# Linq: var someVariable = db.MyTable.Where(entity => entity.FirstName == ...


15

If you want to use something around object identifiers, use at least the standard double quotes: " This works in MySQL, PostgreSQL, SQL Server, Oracle, etc. etc. For MySQL you might need the SQL mode ansi_quotes, depending on the default configuration: SET sql_mode = 'ANSI_QUOTES'; Backticks ` are only used in MySQL, you learn a type of SQL that won't ...


13

If you want to get the exact count of rows in an efficient manner, then COUNT(*) is it. The ANSI standard (look for "Scalar expressions 125") states that COUNT(*) give the row count of a table: it is intended to be optimised from the start. If COUNT(*) is specified, then the result is the cardinality of T. A ROW_NUMBER() function isn't a practical ...


13

SELECT col1, col2, col3, 1 AS query_id FROM players WHERE username='foobar' union all select null, null, null, 1 where not exists (select 1 from players where username = 'foobar'); Or as an alternative (might be faster as no second subselect is required): with qid (query_id) as ( values (1) ) select p.*, ...


12

In Relational algebra, projection means collecting a subset of columns for use in operations, i.e. a projection is the list of columns selected. In a query optimiser step, the projection will manifest itself as a buffer or spool area of some description containing a subset of the columns from the underlying table or operator, or a logical view based on ...


12

Is this what you're aiming for? Make sure the fields that are being compared are comparable (i.e. both fields are numeric, text, boolean, etc). SELECT * FROM Individual INNER JOIN Publisher ON Individual.IndividualId = Publisher.IndividualId WHERE Individual.IndividualId = (SELECT someID FROM table WHERE blahblahblah) If you wish to select based on ...


11

The order of the rows in the absence of ORDER BY clause may be: different between any two storage engines; if you use the same storage engine, it might be different between any two versions of the same storage engine; Example here, scroll down to "Ordering of Rows". if the storage engine version is the same, but MySQL version is different, it might be ...


11

There are actually two problems with the query. The first is Max('Row') will return the string 'Row'. The Second is your subquery needs an alias. Try like this: SELECT MAX(Row) FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) Row FROM USERS) UserQuery UPDATE: I guess there are actually 3 problems with this query :). The 3rd being, count() is a ...


11

Derived table SELECT EMail, hashbytes('SHA1', EMail) AS HashedEmail FROM ( SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as EMail FROM sometable ) foo or CTE: ;WITH cEMail AS ( SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as EMail FROM sometable ) SELECT EMail, hashbytes('SHA1', EMail) ...


11

Performance: A query with SELECT * will probably never be a covering query (Simple talk explanation, Stack Overflow explanation). Future-proofing: Your query might return all seven columns today but if someone adds five columns over the next year then in a year your query is returning twelve columns, wasting IO and CPU. Indexing: If you want your views and ...


10

It is not quite true that NOLOCK means placing no locks at all. Queries under this hint will still take Sch-S locks and (possibly HOBT locks). Under read committed isolation level SQL Server will (usually) take row level S locks and release them as soon as the data is read. These are incompatible with the X locks held on uncommited updates and thus prevent ...


10

Try using the IF function: SELECT IF(`gu`.`StoppingUnitEventME`=`ese`.`MonitoringElement`,TRUE,FALSE) FROM ... or SELECT IF(`gu`.`StoppingUnitEventME`=`ese`.`MonitoringElement`,1,0) FROM ... Even without the IF function, running mysql> select ('rolando' = 'rolando') str_compare; +-------------+ | str_compare | +-------------+ | 1 | ...


10

by default, MySQL does not consider the case of the strings This is not quite true. Whenever you create database in MySQL, the database/schema has a character set and a collation. Each character set has a default collation; see here for more information. The default collation for character set latin1, which is latin1_swedish_ci, happens to be ...


10

Hierarchical queries, as those recursive queries are known, are not supported for MySQL. They are however supported in Oracle, Microsoft SQL Server, DB2, and PostgreSQL, amongst others. If you need a workaround, you can find a dynamic (and thus, potentially dangerous) trick here: http://stackoverflow.com/questions/8104187/mysql-hierarchical-queries You ...


10

Would you believe... SELECT col1, MIN(col2), MAX(col2), MIN(col3), MAX(col3) FROM table1 GROUP BY col1; ...? each row includes the first value of col2 and col3 for each unique value of col. That assertion is not exactly true. That may be what you're seeing, but do not assume this to be meaningful and do not write code based on this ...


9

Both posts are wrong syntax. I've -1 in the first from SO and left a comment on the second. Create a table SELECT * INTO PlayerBackups FROM NhlPlayer Inserts to an existing table INSERT PlayerBackups SELECT * FROM PlayerBackups


9

Projection refers to that subset of the set of all columns found in a table, that you want returned. It can range anywhere from 0** up to the complete set. There are two "sets" in a table that correspond to a table's two dimensions. Each table has a set of columns as well as a set of rows. Each individual value in a table can be found at a specific ...


9

Erwin: I would concur with the idea that USING causing rigid ordering could well create many edge cases where optimal plans would be ruled out. I recently helped someone out who had something like this in his query: LEFT JOIN ( a JOIN b ON a.id = b.a_id JOIN c ON b.c_id = c.id ) ON a.id = something.a_id LEFT JOIN ( table1 t1 ...


9

SELECT type, GROUP_CONCAT( CASE WHEN info = 'yes' THEN name ELSE NULL END ORDER BY id ASC SEPARATOR ' ') AS list_with_info, GROUP_CONCAT( CASE WHEN info = 'no' THEN name ELSE NULL END ORDER BY id ASC SEPARATOR ' ') AS list_without_info FROM table1 GROUP BY type ; Tested at SQL-Fiddle: test-1 ...


9

No, you can specify the 'params' (the parts of the where clause) in any order and the query optimizer will handle it. The optimizer will do the filtering in the order that it estimates is most efficient, but note that this is more complex than just choosing which order to filter: filtering might be done before or after joining for example. You can't exactly ...


9

In SQL Server 2012, (or any version from 2005 up), using SELECT *... is only a possible performance problem in the top-level SELECT statement of a query. So it is NOT a problem in Views(*), in subqueries, in EXIST clauses, in CTEs, nor in SELECT COUNT(*).. etc., etc. Note, that this is probably also true for Oracle, and DB2, and maybe PostGres (not sure), ...



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