Hot answers tagged

51

The query is syntactically correct SQL even if table_b does not have a name column. The reason is scope resolution. When the query is parsed, it is first checked whether table_b has a name column. Since it doesn't, then table_a is checked. It would throw an error only if neither of the tables had a name column. Finally the query is executed as: select a....


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

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 ...


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 ...


28

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


28

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

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, ...


21

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 ...


14

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 ...


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 ...


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

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 | +------...


11

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 case-...


11

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 ...


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

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 ...


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 ...


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

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

Doing a JOIN is one thing you might need. SELECT l.id, l.time, r.time FROM idtimes AS l LEFT JOIN idtimes AS r ON l.id = r.id I suppose the outer join is deliberate, and you want to be getting nulls. More on that later. WHERE l.time < r.time ORDER BY l.id ASC, l.time ASC; You only want the r. row that has the lowest (MIN) time that is higher ...


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 ...



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