All Questions
42,704
questions
5049votes
27answers
2.5mviews
What is the difference between "INNER JOIN" and "OUTER JOIN"?
Also how do LEFT JOIN, RIGHT JOIN and FULL JOIN fit in?
- 55.3k
2747votes
7answers
1.0mviews
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 ...
- 80.1k
1664votes
27answers
3.0mviews
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 ...
- 38.1k
1494votes
7answers
299kviews
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 ...
1298votes
17answers
1.1mviews
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 SQLite 3 command line tool?
- 47.5k
865votes
21answers
1.3mviews
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 ...
- 16.5k
738votes
7answers
1.5mviews
'IF' in 'SELECT' statement - choose output value based on column values
SELECT id, amount FROM report
I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?
- 13.2k
683votes
23answers
799kviews
Solutions for INSERT OR UPDATE on SQL Server
Assume a table structure of MyTable(KEY, datafield1, datafield2...).
Often I want to either update an existing record, or insert a new record if it doesn't exist.
Essentially:
IF (key exists)
run ...
- 28.9k
635votes
11answers
394kviews
Skip certain tables with mysqldump
Is there a way to restrict certain tables from the mysqldump command?
For example, I'd use the following syntax to dump only table1 and table2:
mysqldump -u username -p database table1 table2 > ...
618votes
18answers
924kviews
How do I restore a dump file from mysqldump?
I was given a MySQL database file that I need to restore as a database on my Windows Server 2008 machine.
I tried using MySQL Administrator, but I got the following error:
The selected file was ...
- 54.5k
580votes
9answers
392kviews
What is the difference between Left, Right, Outer and Inner Joins? [duplicate]
I am wondering how to differentiate all these different joins ...
- 21.1k
449votes
33answers
928kviews
How to select the nth row in a SQL database table?
I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native ...
- 19k
447votes
26answers
1.4mviews
MySQL Error: : 'Access denied for user 'root'@'localhost'
Consider:
./mysqladmin -u root -p** '_redacted_'
Output (including typing the password):
Enter password:
mysqladmin: connect to server at 'localhost' failed error:
'Access denied for user 'root'@'...
- 4,703
396votes
10answers
286kviews
Best database field type for a URL
I need to store a url in a MySQL table. What's the best practice for defining a field that will hold a URL with an undetermined length?
- 7,539
387votes
10answers
840kviews
How to replace a string in a SQL Server Table Column
I have a table (SQL Sever) which references paths (UNC or otherwise), but now the path is going to change.
In the path column, I have many records and I need to change just a portion of the path, ...
- 6,954