can I write:  select * from table where id = 1   or should I write:  SELECT * FROM table WHERE id = 1   or no difference?
5565
Is SQL case sensitive.  I've used MySQL and SQL Server which both seem to be case in-sensitive.  Is this always the case?  Does the standard define case-sensitivity?
103055
Please look at this code:  create table #t1(   id int identity (1,1),   val varchar(10) );   insert into #t1 values ('a'); insert into #t1 values ('b'); insert into #t1 values ('c'); insert into #t1 values ('d');   Now, whenever you execute this  select *,      ( select top 1 val from #t1 order by NEWID()) rnd  from #t1 order by 1;   you will get a result with where all rows have the same random value. e.g.  id          val        rnd ----------- ---------- ---------- 1           a          b 2           b          b 3           c          b 4           d          b   I know a way using a cursor to loop throw the rows and get different random values, but that is not performant.   A clever solution to this is   select t1.id, t1.val, t2.val from #t1 t1     join (select *, ROW_NUMBER() over( order by NEWID()) lfd from #t1) as t2 on  t1.id = t2.lfd    But I simplified the query. The real query looks more like  select *,      ( select top 1 val from t2 where t2.x &lt;&gt; t1.y order by NEWID()) rnd  from t1 order by 1;   and the simple solution doesn't fit. I'm looking for a way to force repeated evaluation of  ( select top 1 val from #t1 order by NEWID()) rnd    without the use of cursors.  Edit: Wanted output:  perhaps 1 call  id          val        rnd ----------- ---------- ---------- 1           a          c 2           b          c 3           c          b 4           d          a   and a second call  id          val        rnd ----------- ---------- ---------- 1           a          a 2           b          d 3           c          d 4           d          b   The value for each row just should be a random value independent from the other rows  Here is the cursor version of the code:  CREATE TABLE #res ( id INT, val VARCHAR(10), rnd VARCHAR(10));  DECLARE @id INT DECLARE @val VARCHAR(10) DECLARE c CURSOR FOR SELECT id, val FROM #t1 OPEN c FETCH NEXT FROM c INTO @id, @val WHILE @@FETCH_STATUS = 0 BEGIN     INSERT INTO #res     SELECT @id, @val, ( SELECT TOP 1 val FROM #t1 ORDER BY NEWID()) rnd      FETCH NEXT FROM c INTO @id, @val END CLOSE c DEALLOCATE c  SELECT * FROM #res
100170
When writing a LINQ query with multiple "and" conditions, should I write a single where clause containing &amp;&amp; or multiple where clauses, one for each conditon?  static void Main(string[] args) {     var ints = new List&lt;int&gt;(Enumerable.Range(-10, 20));      var positiveEvensA = from i in ints                          where (i &gt; 0) &amp;&amp; ((i % 2) == 0)                          select i;      var positiveEvensB = from i in ints                          where i &gt; 0                          where (i % 2) == 0                          select i;      System.Diagnostics.Debug.Assert(positiveEvensA.Count() ==                                           positiveEvensB.Count()); }   Is there any difference other than personal preference or coding style (long lines, readability, etc.) between positiveEvensA and positiveEvensB?  One possible difference that comes to mind is that different LINQ providers may be able to better cope with multiple wheres rather than a more complex expression; is this true?
30023
Why would someone use WHERE 1=1 AND &lt;conditions&gt; in a SQL clause (Either SQL obtained through concatenated strings, either view definition)  I've seen somewhere that this would be used to protect against SQL Injection, but it seems very weird.  If there is injection WHERE 1 = 1 AND injected OR 1=1 would have the same result as injected OR 1=1.  Later edit: What about the usage in a view definition?    Thank you for your answers.  Still, I don't understand why would someone use this construction for defining a view, or use it inside a stored procedure.  Take this for example:  CREATE VIEW vTest AS SELECT FROM Table WHERE 1=1 AND table.Field=Value
61005
If I have an UPDATE statement that does not actually change any data (because the data is already in the updated state). Is there any performance benefit in putting a check in the WHERE clause to prevent the update?  For example would there be any difference in execution speed between UPDATE 1 and UPDATE 2 in the following:  CREATE TABLE MyTable (ID int PRIMARY KEY, Value int); INSERT INTO MyTable (ID, Value) VALUES     (1, 1),     (2, 2),     (3, 3);  -- UPDATE 1 UPDATE MyTable SET     Value = 2 WHERE     ID = 2     AND Value &lt;&gt; 2; SELECT @@ROWCOUNT;  -- UPDATE 2 UPDATE MyTable SET     Value = 2 WHERE     ID = 2; SELECT @@ROWCOUNT;  DROP TABLE MyTable;   The reason I ask is that I need the row count to include the unchanged row so I know whether to do an insert if the ID does not exist. As such I used the UPDATE 2 form. If there is a performance benefit to using the UPDATE 1 form, is it possible to get the row count that I need somehow?
3054
Is there any RDBMS that implements something like SELECT * EXCEPT?  What I'm after is getting all of the fields except a specific TEXT/BLOB field, and I'd like to just select everything else.  Almost daily I complain to my coworkers that someone should implement this...  It's terribly annoying that it doesn't exist.  Edit: I understand everyone's concern for SELECT *.  I know the risks associated with SELECT *.  However, this, at least in my situation, would not be used for any Production level code, or even Development level code; strictly for debugging, when I need to see all of the values easily.  As I've stated in some of the comments, where I work is strictly a commandline shop, doing everything over ssh.  This makes it difficult to use any gui tools (external connections to the database aren't allowed), etc etc.  Thanks for the suggestions though.
66399
I have the following code:  const string Sql =      @"select distinct [name]        from tblCustomers        left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId         where (tblCustomer.Name LIKE '%@SEARCH%' OR tblCustomerInfo.Info LIKE '%@SEARCH%');";  using (var command = new SqlCommand(Sql, Connection)) {            command.Parameters.AddWithValue("@SEARCH", searchString);     ... }   This does not work, I tried this as well:  const string Sql =      @"select distinct [name]       from tblCustomers       left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId        where (tblCustomer.Name LIKE @SEARCH OR tblCustomerInfo.Info LIKE @SEARCH );";  using (var command = new SqlCommand(Sql, Connection)) {            command.Parameters.AddWithValue("@SEARCH", "'%" + searchString + "%'");     ... }   but this does not work as well. What is going wrong? Any suggestions?
70647
Is a   select *  from myView   faster than the query itself to create the view (in order to have the same resultSet):  select * from ([query to create same resultSet as myView])   ?  It's not totally clear to me if the view uses some sort of caching making it faster compared to a simple query.
20387
I have two tables A &amp; B. Table A id | name | num  Table B id | date | roll  id in table B is a foreign key which relates to the id in table A. I want to get the contents of table B where id is given and also along with that data, I need the name whose id is given from table A. NOTE: cannot use multiple queries separated by commas; need to do both the SELECTs in one query.
98204
During my work with databases I noticed that I write query strings and in this strings I have to put several restrictions in the where-clause from a list/array/collection. Should look like this:  select * from customer  where customer.id in (34, 26, ..., 2);   You can simplify this by reducing this to the question that you have collection of strings and want to create a comma-separated list of this strings in just one string.  My approach I have used so far is something like that:  String result = ""; boolean first = true; for(String string : collectionOfStrings) {     if(first) {         result+=string;         first=false;     } else {         result+=","+string;     } }   But this is as you can see very ugly. You cannot see what happens there on the first look, especially when the constructed strings (like every SQL query) is getting complicated.  What is your (more) elegant way?
119372
I am very, very new to MYSQL.I tried to create a table named "option". My SQL Query is :  create table option(   id int not null primary key auto_increment,  choice varchar(30)  )  While executing this query it shows the following error  Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'option( id int not null primary key auto_increment, choice varchar(30) )' at line 1 (0 ms taken)  If I try with the table name as "choice" it is working.  can we have the table name as "option" in mysql?   thanks
12650
I seem to remember that (on Oracle) there is a difference between uttering select count(*) from any_table and select count(any_non_null_column) from any_table.  What are the differences between these two statements, if any?
8207
What is the difference between the EXISTS and IN clause in SQL?  When should we use EXISTS, and when should we use IN?
8469
What is the difference between HAVING and WHERE in an SQL SELECT statement? EDIT: I have marked Steven's answer as the correct one as it contained the key bit of information on the link:  When GROUP BY is not used, HAVING behaves like a WHERE clause  The situation I had seen the WHERE in did not have GROUP BY and is where my confusion started. Of course, until you know this you can't specify it in the question.
53567
I have a query which is meant to show me any rows in table A which have not been updated recently enough. (Each row should be updated within 2 months after "month_no".):  SELECT A.identifier      , A.name      , TO_NUMBER(DECODE( A.month_no              , 1, 200803               , 2, 200804               , 3, 200805               , 4, 200806               , 5, 200807               , 6, 200808               , 7, 200809               , 8, 200810               , 9, 200811               , 10, 200812               , 11, 200701               , 12, 200702              , NULL)) as MONTH_NO      , TO_NUMBER(TO_CHAR(B.last_update_date, 'YYYYMM')) as UPD_DATE   FROM table_a A      , table_b B  WHERE A.identifier = B.identifier    AND MONTH_NO &gt; UPD_DATE   The last line in the WHERE clause causes an "ORA-00904 Invalid Identifier" error. Needless to say, I don't want to repeat the entire DECODE function in my WHERE clause. Any thoughts? (Both fixes and workarounds accepted...)
52970
SELECT      Id,     CurrencyIsoCode,      ExpirationDate__c,     (SELECT           Id,          Regional_Starting_Price_2__c       FROM           SBQQ__QuoteLine__c)  FROM      SBQQ__Quote__c   WHERE      Id ='quote_Id'
36635
I have two tables that represent a list of urls and their related word indexes. Here are the table definitions for reference.  desc urllist; +-------+---------------------+------+-----+---------+----------------+ | Field | Type                | Null | Key | Default | Extra          | +-------+---------------------+------+-----+---------+----------------+ | id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment | | url   | text                | NO   |     | NULL    |                | +-------+---------------------+------+-----+---------+----------------+   and  desc wordlocation; +----------+---------------------+------+-----+---------+-------+ | Field    | Type                | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+-------+ | urlid    | bigint(20) unsigned | NO   |     | NULL    |       | | wordid   | bigint(20) unsigned | NO   |     | NULL    |       | | location | int(10) unsigned    | NO   |     | NULL    |       | +----------+---------------------+------+-----+---------+-------+   The software app is a web spider. It crawls a list of urls, extracts those urls, and inserts these into the urllist table. Then, an indexer checks to see what urls have not yet been indexed, and then proceeds to index said urls.  Here is the query I am using to find items in the left table (urllist) that have not yet been indexed in the right table (wordlocation). This query is as suggested on the :  select * from urllist ul  left join wordlocation wl on ul.id = wl.urlid  where wl.urlid IS NULL;   As of this writing my test database has only 600 indexed urls, and the wordlocation table has 1.3 million rows.  However, my CPU is at 100%, and the longest I've waited to see if the query would complete is a half hour (which, it never did by the way).  To be thorough, here is the explanation of the query:  explain select * from urllist ul left join wordlocation wl on ul.id = wl.urlid where wl.urlid IS NULL; +----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+ | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra                   | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+ |  1 | SIMPLE      | ul    | ALL  | NULL          | NULL | NULL    | NULL |   50364 |                         | |  1 | SIMPLE      | wl    | ALL  | NULL          | NULL | NULL    | NULL | 1351371 | Using where; Not exists | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------+   I need this query to finish in seconds, not minutes. Also, I'm worried about scalability. I have 40,000 unique urls that are waiting to be added to the index, so how do I take that into consideration with my table and query design? 400,000 urls?  Just a couple notes about my decisions on the current table structure.  I have no intention of stopping at 400,000 urls, but perhaps bigint(20) is a bit overzealous?   Url as text is for more practical reasons. I index a lot of Asian and other foreign language domains which do not appear as their equivalent Kanji or other characters in the database and frequently take more than 255 characters.  I'm using MySQL. I'm definitely open to suggestions as to better table and query design. Please let me know if I can provide more information.