Updated 4 June 2017 Given that this question/answer have gained some popularity, I figured it was worth an update. When this question was originally posted, MySQL had no support for JSON data types and the support in PostgreSQL was in its infancy. Since 5.7, MySQL now supports a JSON data type (in a binary storage … Read more
No! The same execution plan, look at these two tables: CREATE TABLE table1 ( id INT, name VARCHAR(20) ); CREATE TABLE table2 ( id INT, name VARCHAR(20) ); The execution plan for the query using the inner join: — with inner join EXPLAIN PLAN FOR SELECT * FROM table1 t1 INNER JOIN table2 t2 ON … Read more
Speaking as someone who spent quite a bit of time working with JPA (Java Persistence API, basically the standardized ORM API for Java/J2EE/EJB), which includes Hibernate, EclipseLink, Toplink, OpenJPA and others, I’ll share some of my observations. ORMs are not fast. They can be adequate and most of the time adequate is OK but in … Read more
All commands must be executed while connected to the right database cluster. Make sure of it. Roles are objects of the database cluster. All databases of the same cluster share the set of defined roles. Privileges are granted / revoked per database / schema / table etc. A role needs access to the database, obviously. … Read more
The old syntax, with just listing the tables, and using the WHERE clause to specify the join criteria, is being deprecated in most modern databases. It’s not just for show, the old syntax has the possibility of being ambiguous when you use both INNER and OUTER joins in the same query. Let me give you … Read more
Single quotes are used to indicate the beginning and end of a string in SQL. Double quotes generally aren’t used in SQL, but that can vary from database to database. Stick to using single quotes. That’s the primary use anyway. You can use single quotes for a column alias — where you want the column … Read more
There are really three major reasons: Inefficiency in moving data to the consumer. When you SELECT *, you’re often retrieving more columns from the database than your application really needs to function. This causes more data to move from the database server to the client, slowing access and increasing load on your machines, as well … Read more
I think Zack properly answered the question but just to cover all the bases: Update myTable set MyColumn = NULL This would set the entire column to null as the Question Title asks. To set a specific row on a specific column to null use: Update myTable set MyColumn = NULL where Field = Condition. … Read more
Updated For MS SQL Server 2012 and above USE [master]; DECLARE @kill varchar(8000) = ”; SELECT @kill = @kill + ‘kill ‘ + CONVERT(varchar(5), session_id) + ‘;’ FROM sys.dm_exec_sessions WHERE database_id = db_id(‘MyDB’) EXEC(@kill); For MS SQL Server 2000, 2005, 2008 USE master; DECLARE @kill varchar(8000); SET @kill=””; SELECT @kill = @kill + ‘kill ‘ … Read more
Query A is the same as: select ‘true’ where 3 = 1 or 3 = 2 or 3 = 3 or 3 = null Since 3 = 3 is true, you get a result. Query B is the same as: select ‘true’ where 3 <> 1 and 3 <> 2 and 3 <> null When … Read more