select a.name from student a, student b where a.id = b.id and a.id = 1;   vs  select a.name from student a inner join student b on a.id = b.id and a.id = 1;   Are they actually the same?
15299
Is there any efficiency difference in an explicit vs implicit inner join? For example:  SELECT * FROM table a INNER JOIN table b ON a.id = b.id;   vs.  SELECT a.*, b.* FROM table a, table b WHERE a.id = b.id;
49893
Found the following in an Oracle-based application that we're migrating (generalized):  SELECT     Table1.Category1,     Table1.Category2,     count(*) as Total,     count(Tab2.Stat) AS Stat FROM Table1, Table2 WHERE (Table1.PrimaryKey = Table2.ForeignKey(+)) GROUP BY Table1.Category1, Table1.Category2   What does (+) do in a WHERE clause? I've never seen it used like that before.
4465
Is there a measurable performance difference between using INT vs. VARCHAR as a primary key in MySQL? I'd like to use VARCHAR as the primary key for reference lists (think US States, Country Codes) and a coworker won't budge on the INT AUTO_INCREMENT as a primary key for all tables.   My argument, as detailed , is that the performance difference between INT and VARCHAR is negligible, since every INT foreign key reference will require a JOIN to make sense of the reference, a VARCHAR key will directly present the information.  So, does anyone have experience with this particular use-case and the performance concerns associated with it?
3565
After reading a couple of answers and comments on some SQL questions here, and also hearing that a friend of mine works at a place which has a policy which bans them, I'm wondering if there's anything wrong with using backticks around field names in MySQL.  That is:  SELECT `id`, `name`, `anotherfield` ... -- vs -- SELECT id, name, anotherfield ...
58553
What is the difference between LEFT JOIN and LEFT OUTER JOIN?
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
119219
Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient?  TL;DR: If my joined query takes longer than running individual queries, is this my fault or is this to be expected?  First of, I am not very database savvy, so it may be just me, but I have noticed that when I have to get information from multiple tables, it is "often" faster to get this information via multiple queries on individual tables (maybe containing a simple inner join) and patch the data together on the client side that to try to write a (complex) joined query where I can get all the data in one query.  I have tried to put one extremely simple example together:    Schema Setup:  CREATE TABLE MASTER  ( ID INT NOT NULL , NAME VARCHAR2(42 CHAR) NOT NULL , CONSTRAINT PK_MASTER PRIMARY KEY (ID) );  CREATE TABLE DATA ( ID INT NOT NULL , MASTER_ID INT NOT NULL , VALUE NUMBER , CONSTRAINT PK_DATA PRIMARY KEY (ID) , CONSTRAINT FK_DATA_MASTER FOREIGN KEY (MASTER_ID) REFERENCES MASTER (ID) );  INSERT INTO MASTER values (1, 'One'); INSERT INTO MASTER values (2, 'Two'); INSERT INTO MASTER values (3, 'Three');  CREATE SEQUENCE SEQ_DATA_ID;  INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.3); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.5); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.7); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 2, 2.3); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 3, 3.14); INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 3, 3.7);   Query A:  select NAME from MASTER where ID = 1   :  | NAME | -------- |  One |   Query B:  select ID, VALUE from DATA where MASTER_ID = 1   :  | ID | VALUE | -------------- |  1 |   1.3 | |  2 |   1.5 | |  3 |   1.7 |   Query C:  select M.NAME, D.ID, D.VALUE  from MASTER M INNER JOIN DATA D ON M.ID=D.MASTER_ID where M.ID = 1   :  | NAME | ID | VALUE | --------------------- |  One |  1 |   1.3 | |  One |  2 |   1.5 | |  One |  3 |   1.7 |   Of course, I didn't measure any performance with these, but one may observe:   Query A+B returns the same amount of usable information as Query C. A+B has to return 1+2x3==7 "Data Cells" to the client C has to return 3x3==9 "Data Cells" to the client, because with the join I naturally include some redundancy in the result set.   Generalizing from this (as far fetched as it is):   A joined query always has to return more data than the individual queries that receive the same amount of information. Since the database has to cobble together the data, for large datasets one can assume that the database has to do more work on a single joined query than on the individual ones, since (at least) it has to return more data to the client.  Would it follow from this, that when I observe that splitting a client side query into multiple queries yield better performance, this is just the way to go, or would it rather mean that I messed up the joined query?
14134
I had to write a simple query where I go looking for people's name that start with a B or a D :   SELECT s.name  FROM spelers s  WHERE s.name LIKE 'B%' OR s.name LIKE 'D%' ORDER BY 1   I was wondering if there is a way to rewrite this to become more performant. So I can avoid or and / or like?
16262
I have a table (t0), in my Postgres DB, with data that looks something like this:  t1_id  t2_id 1      1       2      1 2      1 4      null 4      null 5      null   And I have a query to return my desired results of:  t1_id  t2_id 1      1 4      null 5      null   My query looks something like this:  (   SELECT DISTINCT ON (t2_id) t1_id, t2_id   FROM t0   WHERE t2_id IS NOT NULL ) UNION ALL (   SELECT DISTINCT ON (t1_id) t1_id, t2_id   FROM t0   WHERE t2_id IS NULL )   Is there a faster way to do an operation like this? It's not too bad, but I'm doing it in several places (with joins) and all these repeated queries seems to slow stuff down a bit. Seems like there must be a better way.  Here's the query in fiddle form:
22320
I've heard that SELECT * is generally bad practice to use when writing SQL commands because it is more efficient to SELECT columns you specifically need.  If I need to SELECT every column in a table, should I use   SELECT * FROM TABLE   or   SELECT column1, colum2, column3, etc. FROM TABLE   Does the efficiency really matter in this case? I'd think SELECT * would be more optimal internally if you really need all of the data, but I'm saying this with no real understanding of database.  I'm curious to know what the best practice is in this case.  UPDATE: I probably should specify that the only situation where I would really want to do a SELECT * is when I'm selecting data from one table where I know all columns will always need to be retrieved, even when new columns are added.   Given the responses I've seen however, this still seems like a bad idea and SELECT * should never be used for a lot more technical reasons that I ever though about.
5834
Take the representation of an Address, here below is a complete and very detailed implementation:    here instead is a quick implementation (which more or less contains the same fields, imagine all fields in the former are contained also in the second)    If I were to decide which is the closer to a normalized and academically correct one I'd say the first one, however if I were to start a project I would go with the second.  Do you agree with this consideration? And if yes, how does one deal with this fact?   start with an easy database and as soon as it is time improve it into a more normalized/academical database. start with something as close as possible to the academical database stick with the quick and dirty solution
110155
If I have a query like:  Select EmployeeId  From Employee  Where EmployeeTypeId IN (1,2,3)   and I have an index on the EmployeeTypeId field, does SQL server still use that index?
12766
I have the following query:  select column_name, count(column_name) from table group by column_name having count(column_name) &gt; 1;   What would be the difference if I replaced all calls to count(column_name) to count(*)?  This question was inspired by .    To clarify the accepted answer (and maybe my question), replacing count(column_name) with count(*) would return an extra row in the result that contains a null and the count of null values in the column.
405
I have two tables in my database:  NEWS table with columns:   id - the news id  user - the user id of the author)   USERS table with columns:   id - the user id   I want to execute this SQL:   SELECT * FROM news JOIN users ON news.user = user.id    When I get the results in PHP I would like to get associative array and get column names by $row['column-name']. How do I get the news ID and the user ID, having the same column name?
24654
Is there any performance difference between these two queries?  select name from Account where id = :myId  select name from Account where id = :myId limit 1   Or, I guess more general: does salesforce stop searching after first hit when there's a unique value as a condition?
86274
Here's a scenario:  Someone asked about "How to build a SQL query to get count and blah blah blah..."  His only tag is . I added the  and  tags to it.  Someone else answered with a nice SQL statement that has a "group by" statement which seems "logically inevitable".  I proceed to add a tag . I did that on the basis that someone who searches for  will be able to find this post useful, just as I did. My concern is, it wasn't described at all in the question, nor was it implied, unlike  and .  What is the community opinion?  The scenario above is a real one . And the one who answered it nicely is Jon Skeet.
6644
I've got two tables:  TableA ------ ID, Name  TableB ------ ID, SomeColumn, TableA_ID (FK for TableA)   The relationship is one row of TableA - many of TableB.  Now, I want to see a result like this:  ID     Name      SomeColumn  1.     ABC       X, Y, Z (these are three different rows) 2.     MNO       R, S   This won't work (multiple results in a subquery):  SELECT ID,        Name,         (SELECT SomeColumn FROM TableB WHERE F_ID=TableA.ID) FROM TableA   This is a trivial problem if I do the processing on the client side. But this will mean I will have to run X queries on every page, where X is the number of results of TableA.   Note that I can't simply do a GROUP BY or something similar, as it will return multiple results for rows of TableA.   I'm not sure if a UDF, utilizing COALESCE or something similar might work?