Tag Info

Hot answers tagged

37

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


26

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

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


20

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


16

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

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


15

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


15

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


12

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


12

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


11

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


11

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

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

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

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


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

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


9

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


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


8

"it depends" Using a table variable or temp table with requires overhead of creating populating this object However, if the you require multiple processing steps then this is small compared to querying the same data over and over, especially as the query gets more complex. Also, for multiple steps, using a table variable or temp table means working on the ...


8

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


7

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


7

DML can be considered to exclude SELECT statements. The Wikepidia.org entry for “Data Manipulation Launguage” describes it as follows: The purely read-only SELECT query statement is classed with the 'SQL-data' statements2 and so is considered by the standard to be outside of DML. The SELECT ... INTO form is considered to be DML because it ...


7

You do manipulate and restrict data: GROUP BY, ORDER BY, TOP, JOIN, lock hints, etc You just don't change the database state when you do. It boils down to whether you read "manipulate" to include "change state"


7

Without any more specifics, this is all I can offer: Do not use the visual designers. You may be tempted to use "Edit Top 200 Rows" (previously "Open Table") in Management Studio to "edit" data like a spreadsheet. Resist the temptation. These designers are full of bugs, hold unnecessary locks on the table, pick an arbitrary 200 rows until you massage the ...


7

Similar to @ypercubes but to get one row without 3 separate queries select count(CASE WHEN pub_id = '1389' THEN title_id END) as algodata, count(CASE WHEN pub_id = '0877' THEN title_id END) as binnet, count(CASE WHEN pub_id = '0736' THEN title_id END) as newmoon from titles where pub_id IN ('1389', '0877', '0736') Also, decide if your ...


7

Here is the query SELECT id,name FROM (SELECT name,MAX(id) id FROM `mytable` GROUP BY name) A ORDER BY id DESC LIMIT 2; Here is some sample data mysql> use test Database changed mysql> drop table if exists mytable; Query OK, 0 rows affected (0.03 sec) mysql> create table mytable -> (id int not null auto_increment, -> name ...


7

Prior to MySQL 5.6, the inner query is executed once per entry in the outer row and is easy to prove: mysql> SELECT COUNT(*) FROM sample_data; +----------+ | COUNT(*) | +----------+ | 8 | +----------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(*) FROM sample_data WHERE id = (SELECT SLEEP(1) ); +----------+ | COUNT(*) | +----------+ | 0 ...


7

First things first, I notice that your 'what I do now' query: SELECT TOP (1) ca.SensorValue, ca.Date FROM sys.partitions AS p CROSS APPLY ( SELECT TOP (1) v.Date, v.SensorValue FROM SensorValues AS v WHERE $PARTITION.SensorValues_Date_PF(v.Date) = p.[partition_number] AND v.DeviceId = @fDeviceId ...


7

CREATE TABLE `user_mv` (id INT AUTO_INCREMENT PRIMARY KEY) SELECT `user`.`firstname` as `firstname`, `user`.`lastname` as `lastname`, `user`.`lang` as `lang`, `user`.`name` as `user_name`, `group`.`name` as `group_name` from `user` inner join `user_groups` on (`user`.`user_id`=`user_groups`.`user_id`) left join `group` on ...



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