Skip to main content

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.

Filter by
Sorted by
Tagged with
5239 votes
27 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?
Chris de Vries's user avatar
4283 votes
40 answers
5.4m 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 ...
jamesmhaley's user avatar
  • 45.1k
3282 votes
45 answers
3.8m views

How to 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?
Mathias's user avatar
  • 33.9k
2888 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 ...
Xenph Yan's user avatar
  • 83.7k
2769 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']; ...
2471 votes
34 answers
3.8m 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 ...
Alex's user avatar
  • 35.6k
2424 votes
48 answers
3.3m 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?
JohnnyM's user avatar
  • 29.3k
2188 votes
47 answers
3.8m 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?
Eddie Groves's user avatar
  • 34.5k
2183 votes
2 answers
2.2m 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?
Lion King's user avatar
  • 33.5k
2033 votes
4 answers
3.5m 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 ("...
user avatar
2018 votes
21 answers
1.8m 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 ...
David Wolever's user avatar
1949 votes
47 answers
1.8m 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.
Freewind's user avatar
  • 197k
1857 votes
41 answers
578k 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 ...
1849 votes
27 answers
3.4m 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 ...
Claude Houle's user avatar
  • 42.1k
1845 votes
35 answers
2.6m 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 ...
Ferdeen's user avatar
  • 21.6k
1795 votes
30 answers
4.6m 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
Eric Labashosky's user avatar
1771 votes
36 answers
4.1m views

Find all tables containing column with specified name

Is it possible to query for table names which contain columns being LIKE '%myName%'
gruber's user avatar
  • 29.4k
1753 votes
14 answers
2.2m 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 ...
nettoon493's user avatar
  • 18.1k
1706 votes
19 answers
1.6m views

What is the difference between UNION and UNION ALL?

What is the difference between UNION and UNION ALL?
Brian G's user avatar
  • 54.5k
1662 votes
27 answers
2.1m 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 ...
Majid Fouladpour's user avatar
1641 votes
19 answers
2.2m 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 ...
Ant Swift's user avatar
  • 20.8k
1641 votes
27 answers
1.9m 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.
homerun's user avatar
  • 20.5k
1603 votes
8 answers
341k 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 ...
1520 votes
3 answers
2.2m 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?
Alex Gordon's user avatar
  • 59.3k
1488 votes
16 answers
910k 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, ...
Dean Rather's user avatar
  • 32.1k
1443 votes
7 answers
1.0m views

What is the 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 ...
driis's user avatar
  • 163k
1390 votes
19 answers
1.3m 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?
izb's user avatar
  • 51.2k
1387 votes
15 answers
1.8m 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 ...
tim_wonil's user avatar
  • 15.5k
1375 votes
15 answers
1.2m views

How to get the identity of an inserted row?

How can I 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. How do these differ, and ...
Oded's user avatar
  • 496k
1352 votes
16 answers
2.5m 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 ...
Mathieu Longtin's user avatar
1334 votes
34 answers
1.2m 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 ...
Vijay Dev's user avatar
  • 27.3k
1215 votes
29 answers
2.6m 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 ...
user avatar
1215 votes
26 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?
vitule's user avatar
  • 15.8k
1171 votes
13 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 ...
Keshan's user avatar
  • 14.6k
1163 votes
47 answers
1.6m 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] ...
user avatar
1140 votes
41 answers
448k 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 ...
Jeff Atwood's user avatar
  • 63.8k
1140 votes
15 answers
1.0m 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. ...
Jeff Meatball Yang's user avatar
1127 votes
21 answers
954k 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.
Hoff's user avatar
  • 39.4k
1125 votes
20 answers
646k 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 ...
Your Common Sense's user avatar
1120 votes
12 answers
722k 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 ...
JCCyC's user avatar
  • 16.4k
1115 votes
24 answers
2.5m 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 ...
DharaPPatel's user avatar
  • 12.4k
1095 votes
12 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 ...
warren's user avatar
  • 33.2k
1045 votes
24 answers
3.1m 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)?
odiseh's user avatar
  • 26.1k
1016 votes
26 answers
1.7m 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 ...
xorpower's user avatar
  • 18.6k
1015 votes
15 answers
4.5m 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 '...
Mario's user avatar
  • 14.5k
999 votes
19 answers
795k 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?
Tarik's user avatar
  • 81k
996 votes
32 answers
993k 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 ...
chillitom's user avatar
  • 25.5k
973 votes
22 answers
1.0m views

SQL JOIN: what is the difference between WHERE clause and ON clause?

What is the difference and what should go in each? If I understand the theory correctly, the query optimizer should be able to use both interchangeably. (Note: this question is not a duplicate of ...
BCS's user avatar
  • 77.6k
946 votes
10 answers
862k 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-...
Ian Boyd's user avatar
  • 253k
931 votes
9 answers
1.4m views

SQL multiple column ordering

How can I sort multiple columns in SQL and in different directions? For instance, 'column1' would be sorted descendingly and 'column2' ascendingly.
Señor Reginold Francis's user avatar

1
2 3 4 5
13456