sql.js
SQLite compiled to JavaScript through Emscripten
What is the difference between INNER JOIN and OUTER JOIN?
How do LEFT JOIN, RIGHT JOIN, and FULL JOIN fit in?
Source: (StackOverflow)
In SQL Server, it's possible to insert into a table using a SELECT statement:
INSERT INTO Table (col, col2, col3)
SELECT col, col2, col3 FROM other_table WHERE sql = 'cool'
Is it also possible to update via a SELECT? I have a temporary table containing the values, and would like to update another table using those values. Perhaps something like this:
UPDATE Table SET col1, col2
SELECT col1, col2 FROM other_table WHERE sql = 'cool'
WHERE Table.id = other_table.id
Source: (StackOverflow)
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
Source: (StackOverflow)
Good Overviews
Generally speaking you're making a decision between fast read times (e.g. nested set) or fast write times (adjacency list). Usually you end up with a combination of the options below that best fit your needs. The following provides some in depth reading:
Options
Ones I am aware of and general features:
- Adjacency List:
- Columns: ID, ParentID
- Easy to implement.
- Cheap node moves, inserts, and deletes.
- Expensive to find level (can store as a computed column), ancestry & descendants (Bridge Table combined with level column can solve), path (Lineage Column can solve).
- Use Common Table Expressions in those databases that support them to traverse.
- Nested Set (a.k.a Modified Preorder Tree Traversal)
- Popularized by Joe Celko in numerous articles and his book Trees and Hierarchies in SQL for Smarties
- Columns: Left, Right
- Cheap level, ancestry, descendants
- Compared to Adjacency List, moves, inserts, deletes more expensive.
- Requires a specific sort order (e.g. created). So sorting all descendants in a different order requires additional work.
- Nested Intervals
- Combination of Nested Sets and Materialized Path where left/right columns are floating point decimals instead of integers and encode the path information. In the later development of this idea nested intervals gave rise to matrix encoding.
- Bridge Table (a.k.a. Closure Table: some good ideas about how to use triggers for maintaining this approach)
- Columns: ancestor, descendant
- Stands apart from table it describes.
- Can include some nodes in more than one hierarchy.
- Cheap ancestry and descendants (albeit not in what order)
- For complete knowledge of a hierarchy needs to be combined with another option.
- Flat Table
- A modification of the Adjacency List that adds a Level and Rank (e.g. ordering) column to each record.
- Expensive move and delete
- Cheap ancestry and descendants
- Good Use: threaded discussion - forums / blog comments
- Lineage Column (a.k.a. Materialized Path, Path Enumeration)
- Column: lineage (e.g. /parent/child/grandchild/etc...)
- Limit to how deep the hierarchy can be.
- Descendants cheap (e.g.
LEFT(lineage, #) = '/enumerated/path')
- Ancestry tricky (database specific queries)
- Multiple lineage columns
- Columns: one for each lineage level, refers to all the parents up to the root, levels down from the items level are set to NULL
- Limit to how deep the hierarchy can be
- Cheap ancestors, descendants, level
- Cheap insert, delete, move of the leaves
- Expensive insert, delete, move of the internal nodes
Database Specific Notes
MySQL
Oracle
PostgreSQL
SQL Server
- General summary
- 2008 offers HierarchyId data type appears to help with Lineage Column approach and expand the depth that can be represented.
Source: (StackOverflow)
What is the best way to get identity of inserted row?
I know about @@IDENTITY and IDENT_CURRENT and SCOPE_IDENTITY but don't understand the pros and cons attached to each.
Can someone please explain the differences and when I should be using each?
Source: (StackOverflow)
I have seen SQL that uses both != and <> for not equal. What is the preferred syntax and why?
I like != because <> reminds me of Visual Basic.
Source: (StackOverflow)
I need to update this table in SQL Server 2005 with data from its 'parent' table, see below:
sale
id (int)
udid (int)
assid (int)
ud
id (int)
assid (int)
sale.assid contains the correct value to update ud.assid. What query will do this? I'm thinking a join but I'm not sure if it's possible.
Source: (StackOverflow)
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
Source: (StackOverflow)
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'];
mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')");
That's because the user can input something like value'); DROP TABLE table;--, and the query becomes:
INSERT INTO `table` (`column`) VALUES('value'); DROP TABLE table;--')
What can be done to prevent this from happening?
Source: (StackOverflow)
I started by googling, and found this article which talks about mutex tables.
I have a table with ~14 million records. If I want to add more data in the same format, is there a way to ensure the record I want to insert does not already exist without using a pair of queries (ie, one query to check and one to insert is the result set is empty)?
Does a unique constraint on a field guarantee the insert will fail if it's already there?
It seems that with merely a constraint, when I issue the insert via php, the script croaks.
Source: (StackOverflow)