Questions tagged [sql]
Structured Query Language (SQL) is a language for querying databases. Questions should include code examples, table structure, sample data, and a tag for the DBMS implementation (e.g. MySQL, PostgreSQL, Oracle, MS SQL Server, IBM DB2, etc.) being used. If your question relates solely to a specific DBMS (uses specific extensions/features), use that DBMS's tag instead. Answers to questions tagged with SQL should use ISO/IEC standard SQL.
665,956
questions
5193
votes
28
answers
2.6m
views
What is the difference between "INNER JOIN" and "OUTER JOIN"?
Also, how do LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN fit in?
- 56.8k
4223
votes
38
answers
5.2m
views
How do I UPDATE from a SELECT in SQL Server?
In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement:
INSERT INTO Table (col1, col2, col3)
SELECT col1, col2, col3
FROM other_table
WHERE sql = 'cool'
Is it ...
- 44.5k
3228
votes
44
answers
3.7m
views
Add a column with a default value to an existing table in SQL Server
How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?
- 33.4k
2867
votes
7
answers
1.1m
views
How does database indexing work? [closed]
Given that indexing is so important as your data set increases in size, can someone explain how indexing works at a database-agnostic level?
For information on queries to index a field, check out How ...
- 83.1k
2773
votes
27
answers
2.2m
views
How can I prevent SQL injection in PHP?
If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example:
$unsafe_variable = $_POST['user_input'];
...
2404
votes
39
answers
3.6m
views
Finding duplicate values in a SQL table
It's easy to find duplicates with one field:
SELECT email, COUNT(email)
FROM users
GROUP BY email
HAVING COUNT(email) > 1
So if we have a table
ID NAME EMAIL
1 John [email protected]
2 Sam ...
- 34.7k
2383
votes
48
answers
3.2m
views
How to concatenate text from multiple rows into a single text string in SQL Server
Consider a database table holding names, with three rows:
Peter
Paul
Mary
Is there an easy way to turn this into a single string of Peter, Paul, Mary?
- 28.8k
2157
votes
46
answers
3.6m
views
How to return only the Date from a SQL Server DateTime datatype
SELECT GETDATE()
Returns: 2008-09-22 15:24:13.790
I want that date part without the time part: 2008-09-22 00:00:00.000
How can I get that?
- 33.9k
2147
votes
2
answers
2.1m
views
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN
in MySQL?
- 32.9k
1986
votes
4
answers
3.3m
views
Inserting multiple rows in a single SQL query? [duplicate]
I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Id and Office.
INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office");
INSERT INTO MyTable VALUES ("...
rits
1957
votes
21
answers
1.7m
views
Select first row in each GROUP BY group?
I'd like to select the first row of each set of rows grouped with a GROUP BY.
Specifically, if I've got a purchases table that looks like this:
SELECT * FROM purchases;
My Output:
id
customer
total
...
- 149k
1895
votes
47
answers
1.7m
views
How to query MongoDB with "like"
I want to query something with SQL's like query:
SELECT * FROM users WHERE name LIKE '%m%'
How can I achieve the same in MongoDB? I can't find an operator for like in the documentation.
- 194k
1818
votes
34
answers
2.5m
views
Insert results of a stored procedure into a temporary table
How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]?
Select all data from BusinessLine into tmpBusLine works fine.
select *
into ...
- 21.3k
1801
votes
27
answers
3.3m
views
Insert into ... values ( SELECT ... FROM ... )
I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the ...
- 41.1k
1794
votes
41
answers
535k
views
Table Naming Dilemma: Singular vs. Plural Names [closed]
Academia has it that table names should be the singular of the entity that they store attributes of.
I dislike any T-SQL that requires square brackets around names, but I have renamed a Users table ...
1777
votes
29
answers
4.5m
views
How do I perform an IF...THEN in an SQL SELECT?
How do I perform an IF...THEN in an SQL SELECT statement?
For example:
SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product
- 29.5k
1717
votes
14
answers
2.0m
views
How can I delete using INNER JOIN with SQL Server?
I want to delete using INNER JOIN in SQL Server 2008.
But I get this error:
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'INNER'.
My code:
DELETE
FROM WorkRecord2
INNER ...
- 17.8k
1716
votes
36
answers
3.8m
views
Find all tables containing column with specified name - MS SQL Server
Is it possible to query for table names which contain columns being
LIKE '%myName%'
?
- 28.8k
1687
votes
19
answers
1.6m
views
What is the difference between UNION and UNION ALL?
What is the difference between UNION and UNION ALL?
- 53.8k
1625
votes
27
answers
2.0m
views
SQL select only rows with max value on a column [duplicate]
I have this table for documents (simplified version here):
id
rev
content
1
1
...
2
1
...
1
2
...
1
3
...
How do I select one row per id and only the greatest rev?
With the above data, the ...
- 29.4k
1599
votes
25
answers
1.8m
views
How to reset AUTO_INCREMENT in MySQL
How can I reset the AUTO_INCREMENT of a field?
I want it to start counting from 1 again.
- 19.9k
1584
votes
8
answers
330k
views
What are the options for storing hierarchical data in a relational database?
Good Overviews
Generally speaking, you're making a decision between fast read times (for example, nested set) or fast write times (adjacency list). Usually, you end up with a combination of the ...
1582
votes
19
answers
2.0m
views
How can I do an UPDATE statement with JOIN in SQL Server?
I need to update this table in SQL Server with data from its 'parent' table, see below:
Table: sale
id (int)
udid (int)
assid (int)
Table: ud
id (int)
assid (int)
sale.assid contains the ...
- 20.1k
1494
votes
3
answers
2.1m
views
Using group by on multiple columns
I understand the point of GROUP BY x.
But how does GROUP BY x, y work, and what does it mean?
- 57.7k
1476
votes
16
answers
883k
views
Can I concatenate multiple MySQL rows into one field?
Using MySQL, I can do something like:
SELECT hobbies FROM peoples_hobbies WHERE person_id = 5;
My Output:
shopping
fishing
coding
but instead I just want 1 row, 1 col:
Expected Output:
shopping, ...
- 31.8k
1402
votes
7
answers
934k
views
Difference between JOIN and INNER JOIN
Both these joins will give me the same results:
SELECT * FROM table JOIN otherTable ON table.ID = otherTable.FK
vs
SELECT * FROM table INNER JOIN otherTable ON table.ID = otherTable.FK
Is there ...
- 162k
1360
votes
18
answers
1.2m
views
How can I list the tables in a SQLite database file that was opened with ATTACH?
What SQL can be used to list the tables, and the rows within those tables in an SQLite database file – once I have attached it with the ATTACH command on the sqlite3 command line tool?
- 50.2k
1354
votes
15
answers
1.2m
views
How to get the identity of an inserted row?
How am I supposed to get the IDENTITY of an inserted row?
I know about @@IDENTITY and IDENT_CURRENT and SCOPE_IDENTITY, but don't understand the implications or impacts attached to each.
Can someone ...
- 490k
1341
votes
14
answers
1.7m
views
How do I escape a single quote in SQL Server?
I am trying to insert some text data into a table in SQL Server 9.
The text includes a single quote '.
How do I escape that?
I tried using two single quotes, but it threw me some errors.
eg. insert ...
- 15k
1313
votes
15
answers
2.3m
views
How do I limit the number of rows returned by an Oracle query after ordering?
Is there a way to make an Oracle query behave like it contains a MySQL limit clause?
In MySQL, I can do this:
select *
from sometable
order by name
limit 20,10
to get the 21st to the 30th rows (skip ...
- 16k
1309
votes
34
answers
1.1m
views
Retrieving the last record in each group - MySQL
There is a table messages that contains data as shown below:
Id Name Other_Columns
-------------------------
1 A A_data_1
2 A A_data_2
3 A A_data_3
4 B ...
- 27k
1202
votes
25
answers
2.4m
views
Get list of all tables in Oracle?
How do I query an Oracle database to display the names of all tables in it?
- 15.6k
1197
votes
29
answers
2.5m
views
SQL Update from One Table to Another Based on a ID Match
I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number so that I am only working with account numbers.
I created a view ...
Boerseun
1146
votes
12
answers
1.3m
views
Insert into a MySQL table or update if exists
I want to add a row to a database table, but if a row exists with the same unique key I want to update the row.
For example:
INSERT INTO table_name (ID, NAME, AGE) VALUES(1, "A", 19);
Let’s ...
- 14.3k
1133
votes
41
answers
437k
views
Parameterize an SQL IN clause
How do I parameterize a query containing an IN clause with a variable number of arguments, like this one?
SELECT * FROM Tags
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count ...
- 63.4k
1121
votes
15
answers
952k
views
When should I use CROSS APPLY over INNER JOIN?
What is the main purpose of using CROSS APPLY?
I have read (vaguely, through posts on the Internet) that cross apply can be more efficient when selecting over large data sets if you are partitioning. ...
- 37.9k
1119
votes
47
answers
1.5m
views
Exclude a column using SELECT * [except columnA] FROM tableA?
We all know that to select all columns from a table, we can use
SELECT * FROM tableA
Is there a way to exclude column(s) from a table without specifying all the columns?
SELECT * [except columnA] ...
uuɐɯǝʃǝs
1106
votes
21
answers
910k
views
Save PL/pgSQL output from PostgreSQL to a CSV file
What is the easiest way to save PL/pgSQL output from a PostgreSQL database to a CSV file?
I'm using PostgreSQL 8.4 with pgAdmin III and PSQL plugin where I run queries from.
- 38.8k
1105
votes
12
answers
708k
views
INNER JOIN ON vs WHERE clause
For simplicity, assume all relevant fields are NOT NULL.
You can do:
SELECT
table1.this, table2.that, table2.somethingelse
FROM
table1, table2
WHERE
table1.foreignkey = table2.primarykey
...
- 16.2k
1091
votes
20
answers
612k
views
Join vs. sub-query
I am an old-school MySQL user and have always preferred JOIN over sub-query. But nowadays everyone uses sub-query, and I hate it; I don't know why.
I lack the theoretical knowledge to judge for ...
- 157k
1081
votes
24
answers
2.3m
views
Search text in stored procedure in SQL Server
I want to search a text from all my database stored procedures. I use the below SQL:
SELECT DISTINCT
o.name AS Object_Name,
o.type_desc
FROM sys.sql_modules m
INNER JOIN
...
- 12.1k
1074
votes
11
answers
1.1m
views
How can I do 'insert if not exists' in MySQL?
I started by googling and found the article How to write INSERT if NOT EXISTS queries in standard SQL which talks about mutex tables.
I have a table with ~14 million records. If I want to add more ...
- 32.7k
1010
votes
23
answers
2.9m
views
How can I get column names from a table in SQL Server?
I want to query the name of all columns of a table. I found how to do this in:
Oracle
MySQL
PostgreSQL
But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?
- 25.5k
983
votes
19
answers
771k
views
Function vs. Stored Procedure in SQL Server
When should I use a function rather than a stored procedure in SQL, and vice versa? What is the purpose of each?
- 79.8k
978
votes
25
answers
1.6m
views
Reset identity seed after deleting records in SQL Server
I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each ...
- 18k
971
votes
16
answers
4.1m
views
SQL SELECT WHERE field contains words
I need a select which would return results like this:
SELECT * FROM MyTable WHERE Column1 CONTAINS 'word1 word2 word3'
And I need all results, i.e. this includes strings with 'word2 word3 word1' or '...
- 14k
969
votes
31
answers
933k
views
How can I list all foreign keys referencing a given table in SQL Server?
I need to remove a highly referenced table in a SQL Server database. How can I get a list of all the foreign key constraints I will need to remove in order to drop the table?
(SQL answers preferable ...
- 24.9k
934
votes
10
answers
815k
views
How to Join to first row
I'll use a concrete, but hypothetical, example.
Each Order normally has only one line item:
Orders:
OrderGUID OrderNumber
========= ============
{FFB2...} STL-7442-1
{3EC6...} MPT-...
- 247k
932
votes
22
answers
982k
views
SQL JOIN - WHERE clause vs. ON clause
After reading it, this is not a duplicate of Explicit vs Implicit SQL Joins.
The answer may be related (or even the same) but the question is different.
What is the difference and what should go in ...
- 75.7k
908
votes
22
answers
1.9m
views
How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?
I have a table of player performance:
CREATE TABLE TopTen (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
home INT UNSIGNED NOT NULL,
`datetime`DATETIME NOT NULL,
player VARCHAR(6) NOT NULL,
...
- 9,781