Is there a performance difference (or any other reason) to use a JOIN instead of a WHERE clause as per the code examples below:  ex1.  select * from table_1, table_2 where table_1.id=table_2.id;   ex2.  select * from table_1     join table_2 on     table_1.id = table_2.id;
9094
Is there a difference in performance (in oracle) between  Select * from Table1 T1  Inner Join Table2 T2 On T1.ID = T2.ID   And  Select * from Table1 T1, Table2 T2  Where T1.ID = T2.ID   ?
17701
I wounder how I could use an alias in a where statement.  Example :  SELECT SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3)  AS Col1 FROM MyTable WHERE Col1 = 'MySearch'   I use MSSQL 2005
790
I'm writing a LINQ to SQL statement, and I'm after the standard syntax for a normal inner join with an ON clause in C#.  How do you represent the following in LINQ to SQL:  select DealerContact.* from Dealer  inner join DealerContact on Dealer.DealerID = DealerContact.DealerID
10493
Do you know a good source to learn how to design SQL solutions?  Beyond the basic language syntax, I'm looking for something to help me understand:   What tables to build and how to link them How to design for different scales (small client APP to a huge distributed website) How to write effective / efficient  / elegant SQL queries
41779
I m trying to understand where we use following Database.Methods. I know if we want to insert a record to an object we use DML Statement. What i want to know is why/where we use Database. methods. It might be silly question. Database.saveResults Database.insert Database.query Database.Query Database.error   really appreciate the inputs. Thanks a ton in advance.
7026
The normal JOIN ... ON ... syntax is well known. But it is also possible to position the ON clause separately from the JOIN that it corresponds to. This is something that is rarely seen in practice, not found in tutorials and I have not found any web resource that even mentions that this is possible.  Here is a script to play around with:  SELECT * INTO #widgets1 FROM (VALUES (1), (2), (3)) x(WidgetID)   SELECT * INTO #widgets2 FROM (VALUES (1, 'SomeValue1'), (2, 'SomeValue2'), (3, 'SomeValue3')) x(WidgetID, SomeValue)  SELECT * INTO #widgetProperties FROM (VALUES     (1, 'a'), (1, 'b'),     (2, 'a'), (2, 'b')) x(WidgetID, PropertyName)   --q1 SELECT w1.WidgetID, w2.SomeValue, wp.PropertyName FROM #widgets1 w1 LEFT JOIN #widgets2 w2 ON w2.WidgetID = w1.WidgetID LEFT JOIN #widgetProperties wp ON w2.WidgetID = wp.WidgetID AND wp.PropertyName = 'b' ORDER BY w1.WidgetID   --q2 SELECT w1.WidgetID, w2.SomeValue, wp.PropertyName FROM #widgets1 w1 LEFT JOIN #widgets2 w2 --no ON clause here JOIN #widgetProperties wp  ON w2.WidgetID = wp.WidgetID AND wp.PropertyName = 'b'  ON w2.WidgetID = w1.WidgetID ORDER BY w1.WidgetID   --q3 SELECT w1.WidgetID, w2.SomeValue, wp.PropertyName FROM #widgets1 w1 LEFT JOIN (     #widgets2 w2 --no SELECT or FROM here     JOIN #widgetProperties wp     ON w2.WidgetID = wp.WidgetID AND wp.PropertyName = 'b') ON w2.WidgetID = w1.WidgetID ORDER BY w1.WidgetID   q1 looks normal. q2 and q3 have these unusual positionings of the ON clause.  This script does not necessarily make much sense. It was hard for me to contrive a meaningful scenario.  So what do these unusual syntax patterns mean? How is this defined? I noticed that not all positions and orderings for the two ON clauses are allowed. What are the rules governing this?  Also is it ever a good idea to write queries like this?
48923
When using JDBC, I often come across constructs like    ResultSet rs = ps.executeQuery(); while (rs.next()) {     int id = rs.getInt(1);     // Some other actions }   I asked myself (and authors of code too) why not to use labels for retrieving column values:    int id = rs.getInt("CUSTOMER_ID");   The best explanation I've heard is something concerning performance. But actually, does it make processing extremely fast? I don't believe so, though I have never performed measurements. Even if retrieving by label would be a bit slower, nevertheless, it provide better readability and flexibility, in my opinion. So could someone give me good explanation of avoiding to retrieve column values by column index instead of column label? What are pros and cons of both approaches (maybe, concerning certain DBMS)?
58553
What is the difference between LEFT JOIN and LEFT OUTER JOIN?
105601
Since varchar anyways allocate space dynamically, my question is whether using varchar(255) be more efficient or save more space compared to using varchar(5000).  If yes, why?
106137
If we have 2 tables  **user** +----+------+------+-----------+ | id | user | pass | gender_id | +----+------+------+-----------+ | 1  | lisa | good |     11    | | 2  | jack | pass |     10    | | 3  | paul | 5555 |     10    | | 4  | rich | none |     10    | +----+------+------+-----------+  **gender** +----+--------+ | id | gender | +----+--------+ | 11 | female | | 10 | male   | +----+--------+   If we join  them like so  SELECT user.id, user.user, user.pass, user.gender_id, gender.gender FROM `user` JOIN `gender` on user.gender_id   The result of query  array(4) {   [0]=&gt;   array(4) {     ["id"]=&gt;     string(3) "2"     ["type"]=&gt;     string(6) "jack"     ["pass"]=&gt;     string(11) "pass"     ["gender"]=&gt;     string(1) "male"     }   [1]=&gt;   array(4) {     ["id"]=&gt;     string(3) "3"     ["type"]=&gt;     string(6) "paul"     ["pass"]=&gt;     string(11) "5555"     ["gender"]=&gt;     string(1) "male"     }   [2]=&gt;   array(4) {     ["id"]=&gt;     string(3) "4"     ["type"]=&gt;     string(6) "rich"     ["pass"]=&gt;     string(11) "none"     ["gender"]=&gt;     string(1) "male"     }   [3]=&gt;   array(4) {     ["id"]=&gt;     string(3) "1"     ["type"]=&gt;     string(6) "lisa"     ["pass"]=&gt;     string(11) "good"     ["gender"]=&gt;     string(1) "female"     }   The order has now changed even though we did not specify this.(user lisa is last) If we change lisa gender_id to 11, and run the same query, the result will be  array(4) {   [0]=&gt;   array(4) {     ["id"]=&gt;     string(3) "1"     ["type"]=&gt;     string(6) "lisa"     ["pass"]=&gt;     string(11) "good"     ["gender"]=&gt;     string(1) "male"     }   [1]=&gt;   array(4) {     ["id"]=&gt;     string(3) "2"     ["type"]=&gt;     string(6) "jack"     ["pass"]=&gt;     string(11) "pass"     ["gender"]=&gt;     string(1) "male"     }   [2]=&gt;   array(4) {     ["id"]=&gt;     string(3) "3"     ["type"]=&gt;     string(6) "paul"     ["pass"]=&gt;     string(11) "5555"     ["gender"]=&gt;     string(1) "male"     }   [3]=&gt;   array(4) {     ["id"]=&gt;     string(3) "4"     ["type"]=&gt;     string(6) "rich"     ["pass"]=&gt;     string(11) "none"     ["gender"]=&gt;     string(1) "male"     }   This result is how the rows are positioned in the table(user lisa first), which is represented correctly in the results as we did not specify any order.  Why is the first result set being ordered when we are not specifying this. It does not seem correct, I want the results to be pulled exactly how they are positioned in the table.  Thanks
8207
What is the difference between the EXISTS and IN clause in SQL?  When should we use EXISTS, and when should we use IN?
42549
All,  So I've got all my select queries in LINQ-to-SQL converted to using CompiledQueries to speed things up.  Works great so far for select statements, but I haven't been able to figure out how to pre-compile insert, update, or delete statements.  Granted, when you're inserting, deleting or updating in LINQ-to-SQL, you have to use the object model.  But obviously somewhere along the way it generates a query, which would be nice to pre-compile and store in a static member.  Is this possible?  What is LINQ performance like for updates, deletes and inserts when its not pre-compiled?  I could see it being a lot faster than the selects, because what they do underneath is a lot simpler and less "dynamic"...
112987
I am trying to use a MERGE statement to insert or delete rows from a table, but I only want to act on a subset of those rows. The documentation for MERGE has a pretty strongly worded warning:     It is important to specify only the columns from the target table that are used for matching purposes. That is, specify columns from the target table that are compared to the corresponding column of the source table. Do not attempt to improve query performance by filtering out rows in the target table in the ON clause, such as by specifying AND NOT target_table.column_x = value. Doing so may return unexpected and incorrect results.   but this is exactly what it appears I have to do to make my MERGE work.  The data I have is a standard many-to-many join table of items to categories (e.g. which items are included in which categories) like so:  CategoryId   ItemId ==========   ====== 1            1 1            2 1            3 2            1 2            3 3            5 3            6 4            5   What I need to do is to effectively replace all rows in a specific category with a new list of items. My initial attempt to do this looks like this:  MERGE INTO CategoryItem AS TARGET USING (   SELECT ItemId FROM SomeExternalDataSource WHERE CategoryId = 2 ) AS SOURCE ON SOURCE.ItemId = TARGET.ItemId AND TARGET.CategoryId = 2 WHEN NOT MATCHED BY TARGET THEN     INSERT ( CategoryId, ItemId )     VALUES ( 2, ItemId ) WHEN NOT MATCHED BY SOURCE AND TARGET.CategoryId = 2 THEN     DELETE ;   This appears to be working in my tests, but I am doing exactly what MSDN explicitly warns me not to do. This makes me concerned that I will run into unexpected problems later on, but I cannot see any other way to make my MERGE only affect rows with the specific field value (CategoryId = 2) and ignore rows from other categories.  Is there a "more correct" way to achieve this same result? And what are the "unexpected or incorrect results" that MSDN is warning me about?
19493
Which of these queries is the faster?  NOT EXISTS:  SELECT ProductID, ProductName  FROM Northwind..Products p WHERE NOT EXISTS (     SELECT 1      FROM Northwind..[Order Details] od      WHERE p.ProductId = od.ProductId)   Or NOT IN:  SELECT ProductID, ProductName  FROM Northwind..Products p WHERE p.ProductID NOT IN (     SELECT ProductID      FROM Northwind..[Order Details])   The query execution plan says they both do the same thing.  If that is the case, which is the recommended form?  This is based on the NorthWind database.  [Edit]  Just found this helpful article:    I think I'll stick with NOT EXISTS.
83334
I've seen a number of people claim that you should specifically name each column you want in your select query.  Assuming I'm going to use all of the columns anyway, why would I not use SELECT *?  Even considering the question **, I don't think this is an exact duplicate as I'm approaching the issue from a slightly different perspective.    One of our principles is to not optimize before it's time.  With that in mind, it seems like using SELECT * should be the preferred method until it is proven to be a resource issue or the schema is pretty much set in stone.  Which, as we know, won't occur until development is completely done.  That said, is there an overriding issue to not use SELECT *?
85275
I have two tables with date and id fields. I want to join on both fields.  I tried   JOIN t2 ON CONCAT(t1.id, t1.date)=CONCAT(t2.id, t2.date)   that works, but it is very slow. is there a better way to do this?
34540
Good evening,  I'm currently up to try to improve the performance of some of my queries. As far as I know, statements like "IN" or "NOT IN" are even faster with a large amount of values if a subquery on an indexed field without conditions is used.  SELECT * FROM table1 WHERE field1 NOT IN (SELECT index_field FROM table2)   When it comes to use conditions at table2, the query becomes realy slow on a large amount of data.  SELECT * FROM table1 WHERE     field1 NOT IN (         SELECT             index_field         FROM table2         WHERE             user_id = '2'     )   I was thinking about using LEFT Join but when I need to to the filtering for the user_id, it also becomes slow.  How may I solve this problem? Currently, I've no clue about that.  The original query looked sth. like this  SELECT     i.* FROM stream_item si LEFT JOIN user__publisher_item ui     ON ui.user_id = 2     &amp;&amp; ui.item_id = si.item_id INNER JOIN item i     ON i.id = si.item_id WHERE      si.stream_id IN (5,7,8,9,19,24,29,42,43,44)     &amp;&amp; ui.id IS NULL || (         ui.`read` = 0 || ui.saved = 1     ) GROUP BY     si.item_id ORDER BY     si.`found` DESC LIMIT 0,10     id      select_type     table   type        possible_keys                                                                           key                 key_len     ref                 rows        Extra 1       SIMPLE          si      index       PRIMARY,stream_2_item,stream_id_found                                                   stream_2_item       4           \N                  663236      Using temporary; Using filesort 1       SIMPLE          ui      eq_ref      user_id_item_id,user_2_item,user_id_read_saved_hidden,user_id_saved,user_id_hidden      user_id_item_id     8           const,si.item_id    1           Using where 1       SIMPLE          i       eq_ref      PRIMARY                                                                                 PRIMARY             4           si.item_id          1      I have a table which represents the n:m relationship between stream and items. An unique item can be accessible via multiple streams. Based upon this i have a state-table for user dependent states of an item. Currently, the user-state-entry will only be generated, if the item was read or saved the first tim. Otherwise, there is no entry in the user_item table. In the beginning, this query was fine but now, it takes a lot of time. Even if I leave out the "INNER JOIN" it takes 7 seconds on the current tables with around 500k rows each.  The table-structure in the background is as follows:   stream stream_item //n:m relation between stream and items item //unique items stream_group //user-defined groups of multiple streams stream_group_streams //_n:m relation between stream_groups and streams_ user user_item //state-table that holds the user-dependent states of an item - if there is no state, no row exists for an item   I already asked this question at  but added my 2 Edits with further information to late so that I haven't got an answer anymore.