A core SQL statement, SELECT retrieves data from one or more tables or other sources of row set data such as views or table-valued functions.

learn more… | top users | synonyms

39
votes
8answers
9k views

Is select * still a big no-no on SQL Server 2012?

Back in the days of yesteryear, it was considered a big no-no to do select * from table or select count(*) from table because of the performance hit. Is this still the case in later versions of SQL ...
28
votes
1answer
5k views

USING construct in JOIN clause can introduce optimization barriers in certain cases?

It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases. I mean this key word: SELECT * ...
20
votes
2answers
3k views

Why would SELECT * be magnitudes faster than SELECT foo?

Consider a table of values and hashes, like so: +------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
16
votes
5answers
37k views

How can I use a default value in a Select query in PostgreSQL?

I would like to use a default value for a column that should be used if no rows is returned. Is that possible in PostgreSQL? How can I do it? Or is there any other way I can solve this? E.g. ...
16
votes
2answers
103k views

oracle - list users with access to certain tables

I'm sure this has been asked before but I can't seem to find the relevant details for the following. Is there some sort of pre-built table that can do the following (I have used dba_tab_privs but it ...
14
votes
7answers
29k views

Is it possible to mysqldump a subset of a database required to reproduce a query?

Background I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research). Question ...
13
votes
3answers
18k views

What is the default order of records for a SELECT statement in MySQL?

Suppose you have the following table and data: create table t(k int, v int,index k(k)) engine=memory; insert into t values(10, 1), (10, 2), (10, 3); When issuing select * from t where k = 10 with ...
13
votes
2answers
10k views

How to select specific rows if a column exists or all rows if a column doesn't

I'm writing a script that gets a count of rows for a few tables, however for some tables I want to only get a count of rows where a flag is set (in this case active=1). Is there a way I can do this ...
11
votes
4answers
5k views

Benefits of using backtick (`) in MySQL queries?

In MySQL we can create queries with or without the backtick (`) symbol. Example: SELECT * FROM TEST; SELECT * FROM `TEST`; Both works fine in mysql-console. Is there any technical difference ...
10
votes
3answers
3k views

Why do I have to select from the dual table?

This works in the major Relation Database Management Systems most likely to appear on StackOverflow/dba.stackexchange, being SQL Server, MySQL, PostgreSQL and SQLite (WebSQL). select 'abc' abc, 1 ...
9
votes
4answers
15k views

What does the term “projection” refer to in an SQL query (or view definition)

In Oracle's document The Query Optimizer, under View Merging, I found the following information The view merging optimization applies to views that contain only selections, projections, and ...
9
votes
2answers
24k views

How to cast an integer to a boolean in a MySQL SELECT clause?

I'm new here so be kind to me. I have the following scenario: I have many tables which, for the sake of simplicity, are represented in a View in my MySQL database. My problem is that I need a value ...
9
votes
1answer
10k views

Autoincrement primary key in CREATE TABLE … AS SELECT

I created table using a complicated select query via CREATE TABLE ... AS SELECT.... How can I add an autoincrement primary key in this query? For example: create table `user_mv` select ...
8
votes
4answers
27k views

How to do a case sensitive search in WHERE clause?

I want case sensitive search in SQL query. But by default, MySQL does not consider the case of the strings. Any idea on how to do a case sensitive search in SQL query?
8
votes
2answers
35k views

SELECTing multiple columns through a subquery

I am trying to SELECT 2 columns from the subquery in the following query, but unable to do so. Tried creating alias table, but still couldn't get them. SELECT DISTINCT petid, userid, (SELECT ...
7
votes
4answers
5k views

Multiple operations using WITH

Is there a way to execute multiple operations using the WITH statement? Something like WITH T AS ( SELECT * FROM Tbl ) BEGIN OPEN P_OUTCURSOR FOR SELECT * FROM T; SELECT COUNT(*) INTO ...
6
votes
2answers
9k views

How to use variables inside a select (SQL Server)?

If I want to compute a column and use the result in more than 1 column, how do I do this without making the computing twice? Example: SELECT LOWER(SUBSTRING([NAME], 4, 100)) + '@somedomain.com' as ...
6
votes
2answers
369 views

MySQL: Show list of tables with paging in Terminal

I am using MySQL Ver 14.14 Distrib 5.5.44 for debian-linux-gnu. How can I show my tables page wise in a terminal?
6
votes
2answers
516 views

Optimizing WHERE condition for TIMESTAMP field in MySQL SELECT statement

I am working on a schema for an analytics system which tracks usage times, and there is a need to see total usage time in a certain date range. To give a simple example, this type of query would be ...
5
votes
2answers
66k views

Using SELECT in the WHERE clause of another SELECT

I have made a draft remote application on top of libpq for PostrgreSQL. It behaves well, but I have profiled the general functioning of the application. For each final business result that I produce, ...
5
votes
2answers
29k views

How to JOIN two table to get missing rows in the second table

In a simple voting system as CREATE TABLE elections ( election_id int(11) NOT NULL AUTO_INCREMENT, title varchar(255), CREATE TABLE votes ( election_id int(11), user_id int(11), FOREIGN KEYs for ...
5
votes
2answers
12k views

How do I get the current and next greater value in one select?

I have a InnoDB table 'idtimes' (MySQL 5.0.22-log) with columns `id` int(11) NOT NULL, `time` int(20) NOT NULL, [...] with a compound unique key UNIQUE KEY `id_time` (`id`,`time`) so there can ...
5
votes
1answer
4k views

SELECT with large WHERE IN clause taking long time

I'm pretty new at all this so be gentle :). I have large medical DB where I need to extract data for patients based on selected doctor(s). Right now I build a patient list by SELECT patID FROM ...
5
votes
1answer
2k views

Mysql sub query returns more than 1 row

SELECT * FROM wp_posts WHERE ID IN ( (SELECT courses FROM wp_category WHERE CatID =401) OR (SELECT meta_value FROM wp_postmeta WHERE post_id IN (SELECT courses FROM wp_category WHERE CatID =401) ...
5
votes
2answers
63 views

Same record shows up multiple times in ordered paginated query

Some records show up on multiple result "pages" with the following query: SELECT `description`, `wallpaper` FROM `bbr_bar` WHERE `country_id` = '2510769' ORDER BY `online` DESC ...
5
votes
1answer
184 views

Exactly what error is returned by PostgreSQL `FOR UPDATE NOWAIT` when a selected row cannot be locked immediately?

The PostgreSQL 9.4 documentation states that adding the NOWAIT option to a SELECT FOR UPDATE means an error is generated when a row cannot be locked: To prevent the operation from waiting for ...
4
votes
4answers
18k views

Recursive self joins

I have a comments table, which can be simplified down to this: comments ======= id user_id text parent_id where parent_id is nullable, but might be a key for its parent comment. Now, how can I ...
4
votes
3answers
34k views

Getting SELECT to return a constant value even if zero rows match

Consider this select statement: SELECT *, 1 AS query_id FROM players WHERE username='foobar'; It returns the column query_id with value 1 along with a player's other columns. How would ...
4
votes
2answers
2k views

Has SELECT an INSERT clause?

I know the form Select * into new_table from old_table statement. Bur recently I found 2 posts here and here which use it like SELECT * FROM NhlPlayer INSERT INTO PlayerBackups I find nothing in ...
4
votes
2answers
626 views

select specified number of unique IDs where second column is unique

Look at the following example starting from the top row (id=9) and work your way down, selecting a limit of 4 rows that have sec's that we have not yet seen. We "select" id=9 because we don't yet have ...
4
votes
1answer
654 views

Does the order of fields in SELECT query matter when using composite indexing?

I understand that the order of columns in the index itself matters immensely; however, what about the order of columns in the subsequent SELECT queries that make use of that index? For example, if I ...
4
votes
1answer
7k views

Insert into table select * from table vs bulk insert

I just wanted to know that SQL Statement INSERT INTO TABLE1 SELECT * FROM TABLE2, will work same like bulk insert ? If no, Is there any way exclude index while inserting records. Process is inserting ...
4
votes
2answers
42k views

Select column names whose entries are not null

I would like to have a list of those columns of a table that have at least one not-NULL data entries in them. In other words, I would like to get the column names for which the following returns at ...
4
votes
2answers
4k views

show table name + number of records for each table in a mysql innodb database

How to list all tables in the current database, together the number of rows of the table. In other words, can you think of a query to come up with something like this in mysql? ...
4
votes
2answers
49k views

How do I use the IF…ELSE condition in a WHERE clause?

I'm attempting to use the IF...ELSE construct in my WHERE clause to selectively apply conditions to my SELECT. Should this work? CREATE OR REPLACE package body If_Else_Pack IS PROCEDURE Moving ...
4
votes
2answers
3k views

WHERE clause is not filtering on LESS THAN and GREATER THAN or BETWEEN

I am trying to select MTR values that are between the values of 0 and 4. What am I doing wrong? SELECT DATEDIFF(M, TheDate, TheOtherDate) AS MTR FROM MyTable WHERE MyID = 2074163 AND MTR >= 0 ...
4
votes
2answers
172 views

Query parent table and get child tables columns

I have two tables, one inherits the other: CREATE TABLE parent ( col1 INTEGER ); CREATE TABLE child ( col2 INTEGER ) INHERITS( parent ); // Insert some data INSERT INTO parent( col1 ) ...
4
votes
1answer
550 views

Search a Redis Database

Imagine having a tun of hashes in a Redis Database, each representing a user object. How would you get the user with for example the lowest registration date? The user where ts_registered is the ...
4
votes
1answer
107 views

Select columns from 4 tables

First thing, I am totally new to SQL. I saw some tutorials, videos, tried searching but didn't find what I needed. I know it has asked before, but I couldn't find it. What I need to find is basically ...
4
votes
1answer
872 views

Select one name per person where spelling variations exist

I have a source table that contains Employee IDs and names. The employee names may be listed multiple times with variations on the exact spelling (see 'source table' for example - note the middle ...
4
votes
2answers
72 views

Indexed but still slow during query

I have a table that stores REST requests and responses: CREATE TABLE ws_call_logs ( id BIGINT NOT NULL, ws_method_id BIGINT NOT NULL, device_id ...
4
votes
1answer
74 views

How to only update if nothing has changed? I need to validate several things before I insert

Say I have two tables: runners and waypoint_checkins. I want to add waypoint checkin entries but I can never add a waypoint if the runner has finished the race, (see below for schema). Finally. A ...
4
votes
2answers
342 views

Inner Join or direct data save in SQL Server

I am developing a social network site and expecting very good response from people so expecting huge users (maybe more than million in years). My website is holding user information table and all its ...
4
votes
2answers
5k views

SELECT TOP in MySQL

How can I do this in MySQL? SELECT TOP 50 PERCENT * FROM table What's the simplest way to do it without running to separate queries (if possible)?
4
votes
2answers
58 views

MySQL - Order by closest value on multiple columns

I have a table for a game lobby. In this lobby a user gets put in their connection and their level. In my SQL statement I am getting all the users in the lobby which are between +5/-5 of the user's ...
4
votes
2answers
553 views

Select only those records that have different/multiple values for a particular column

Below is an example of my membership table. There some records having multiple values in the email field. I only want to select those records that have multiple email values: Member table ID ...
4
votes
1answer
1k views

Tricky selection of grouped rows, selecting based on values of two distinct but related rows

Say I have a table with the following information (sorted here for display purposes) taskid | reference | centreid | date_out --------------------------------------------------- 10001 | ...
4
votes
2answers
8k views

How to GROUP_CONCAT DISTINCT values in a MySQL query that gets number of records and min/max values?

I'm running MySQL 5.0.88 (Coldfusion8) I have a product search which I'm querying number-of-results as well as min/max prices/rebates across the product table. I also want to include a string of ...
4
votes
1answer
97 views

Fast alternative for “NOT IN”

I have a table A that got a field called id that is the primary key of this table. I also have a table called B which also have a field called id as primary key. Now I want to get all rows from ...
4
votes
1answer
151 views

Mysql: Problem using GREATEST with IFNULL and join

I have a query selecting the greatest value of 2 timestamps. To achieve it I'm calling: SELECT GREATEST( IFNULL( tableb.create_time, 0 ) , IFNULL( tablea.create_time, 0 ) ) I'm using IFNULL ...