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.
20
votes
1answer
941 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 *
...
8
votes
3answers
718 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 ...
7
votes
4answers
905 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 ...
7
votes
3answers
2k 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 ...
7
votes
2answers
19k 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 ...
6
votes
2answers
2k 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 ...
5
votes
3answers
1k 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 ...
5
votes
6answers
6k 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
...
5
votes
4answers
494 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 ...
5
votes
1answer
272 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) ...
4
votes
5answers
6k 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. ...
4
votes
2answers
1k 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
3k 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?
4
votes
1answer
86 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
773 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 ...
4
votes
2answers
2k 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
1answer
195 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
1k 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)?
3
votes
5answers
227 views
Single query to return counts over different IDs in a single record
select count(title_id)as algodata from titles where pub_id =1389
select count(title_id)as binnet from titles where pub_id =0877
select count(title_id)as newmoon from titles where pub_id =0736
...
3
votes
2answers
3k views
Can someone explain why select with nolock will query a potion of updated data?
I was reading the answer from here (from stackoverflow, I think should ask in here)
NOLOCK means placing no locks at all.
Your query may returns portions of data as of before UPDATE and
...
3
votes
3answers
103 views
Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates
We have an unusual database structure (I leave the reasoning for another question), that in it's current form requires a regular truncation and recreation of allot of row (about 1 million).
We're ...
3
votes
2answers
466 views
Select query according to date
We have a table to store the Events.
Table has got a schema like
EventID INT Primary Key
EventName NVARCHAR(100)
StartDate Datetime
EndDate Datetime
So in the front end we want to show the events ...
3
votes
1answer
139 views
Single AND operator and multiple OR operators
I wanted to select a record_id with a user_id of 1 and category_id can be 1, 2, or 3.
Every SELECT statement should return 1 record.
View my sample table here: http://i49.tinypic.com/2ezjtk8.png
How ...
3
votes
1answer
768 views
Any disadvantages of “SQL_BUFFER_RESULT” for MySQL SELECT?
I've been researching the usage of SQL_BUFFER_RESULT. Mostly it is referred to as an aid to reduce table lock issues.
It seems to be a good option to use.
However I cannot seem to find any ...
3
votes
2answers
277 views
Select total number of records from common table expression
We are using common table expressions to temporarily save the search results.
WITH Results (
------
)
AS
(
select ---
)
SELECT * FROM ...
3
votes
2answers
2k 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 ...
3
votes
2answers
261 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 ...
3
votes
1answer
827 views
SELECT LIMIT 1 per column value?
Lets say I have the following table
-----------------------------
| user_id | comment |
-----------------------------
| 2 | thats cool |
| 2 | awesome |
| 3 | ...
3
votes
1answer
38 views
PostgresSQL: Get single attribute of UDT in SELECT statement
I created a user-defined type in a PostgreSQL 9.2 database and am trying in the SELECT statement to get only an attribute of the UDT. However, I don't seem to get my code to work.
Creation of type ...
3
votes
2answers
113 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 ...
3
votes
1answer
151 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 | ...
3
votes
2answers
1k 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 ...
3
votes
3answers
947 views
Does the MySQL primary key affect the efficiency of a SELECT query?
Does the primary key serve any purposes apart from uniquely identifying a specific row? For example, a table with an autoincremented primary INT key could greatly aid in search due to the option of ...
3
votes
1answer
1k views
how to fetch different data on compare rows of two tables
I have two tables in my database, one is user_app table in which user info are stored, table are as:
+--------------+--------------+------+-----+-------------------+-----------------------------+
| ...
3
votes
2answers
283 views
Should I use select count(*) for counting records or having a field like comments_no for tables like post, comments?
In my recent project which is about social networking for an Asian country, I'm in doubt whether I use the below SQL statement for counting records:
SELECT COUNT(id) shareLikeNo FROM ...
2
votes
5answers
220 views
How define select, if bridge pair may have 3 players?
Normaly a pair has 2 players, but if there is an odd number of players at the club, one 'pair' has 3 players.
Table, members:
pair, player_id
1 1
1 2
2 3
2 4
3 5
3 6
4 7
4 8
5 9
5 10
6 11
6 12
6 ...
2
votes
2answers
630 views
Does the SELECT statement count into DML?
Looking at the name Data Manipulation Language (DML) I would assume, that all contained statements are actually for manipulating data.
As far as I know the SELECT statement can only be used to query ...
2
votes
2answers
543 views
Sub-query executes 1 time for a parent query or more?
If i apply a static data in where clause is it faster then applying a sub-query. Example:
(The query returns 5 records as a result set)
SELECT STARTDATE, ENDDATE FROM TEST WHERE STARTDATE = ...
2
votes
1answer
173 views
SELECT multiple sensor values in one query
Background
I have a couple of devices, each with a couple of sensors. I log these every now and then and stores them in a table described below. When someone requests a web page, I fetch a couple of ...
2
votes
1answer
768 views
SQL Select WHERE row updated
USE [RF_WORLD]
GO
/****** Object: Trigger [dbo].[lastdeathtrigg] Script Date: 08/20/2012 10:52:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ...
2
votes
1answer
761 views
Getting unique names when the IDs are different (DISTINCT ?)
I've got this table:
id | name
1 | John
2 | John
3 | Peter
4 | Mike
5 | Mike
And I want to get the unique names, getting the latest one in the list. I could go for:
SELECT DISTINCT name ...
2
votes
3answers
2k views
Return int value from boolean field in SQL select
Is there a way to return an int value for a boolean field in a sql select statement?
2
votes
2answers
57 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 ...
2
votes
2answers
275 views
Select rows with repeat data within certain timeframe
As a follow-up question to my previous question on difficult row categorisation, I have another problem which I'm sure will be easily solvable. After this I plan on reading a good book or two on ...
2
votes
2answers
1k 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 ...
2
votes
2answers
436 views
Is there a benefit/drawback to adding LIMIT 0,1 when performing SELECT on unique key field?
I am using a MySQL database and perform many select like these:
SELECT MyField FROM MyTable WHERE MyKey = 'some_value';
The purpose is to retrieve a field a value if the corresponding row exists. ...
2
votes
3answers
315 views
Count consecutive rows in mysql
I would like to get the count of consecutive rows in MySQL. It is a large database I try to avoiding the joins. Here gadget_id means vehicle and every 20/30 second the vehicle sends the current ...
2
votes
1answer
798 views
Multiple “GROUP_CONCAT”s with WHERE clause
In a table as
id name type info
1 BMW car yes
2 Reno car no
3 IBM electronics no
4 Sony electronics yes
5 Mazda ...
2
votes
2answers
66 views
Are these extra Index fields needed?
I've designed a table as below for users who register a quiz:
CREATE TABLE `registered_quiz` (
`rq_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`user_id` smallint(5) unsigned NOT NULL,
...
2
votes
2answers
197 views
How expensive are SELECT FROM LIKE statements from a design perspective?
I have a situation running out of control regarding an ever increasing number of small static files I need to access randomly in a large directory structure. I MUST reduce the number of those files ...
