I've seen answers to other questions on here and most suggest using a JOIN instead of WHERE in SQL.   I'm new to SQL, having started using Oracle around 6 months ago and I've not once used a JOIN.   I don't do any complex queries by any means. A query I use quite often is below. What would the equivalent be using a JOIN, should I do that and are there any advantages?  SELECT orders.no, orders.created, totals.net FROM orders, totals WHERE orders.no = totals.no   I'm using an ancient version of Oracle; 8i so I believe some of the JOIN are using (+) or something, but never understood it.
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;
90248
Assuming you can't use LINQ for whatever reason, is it a better practice to place your queries in stored procedures, or is it just as good a practice to execute ad hoc queries against the database (say, SQL Server for argument's sake)?
102621
Now there is a question we commonly use this technique to maintain the parent child relation i.e we store all the entities in one tables with a parent_id column and all top most parents have 0 in the parent_id column this is a good and normalized technique i agree but there is a disadvantage also, it’s slow and inefficient. This is mainly caused by the recursion like for each parent we have to run the query again and again to make a tree  SELECT id FROM `table` WHERE parent_id=something   I have looked at the solutions some might try to do it with any programming language by running query again and again which makes a loads on server , Some have provided the stored procedure but that also involves the recursion.  So my question is can we do it with one database query for the tree(joins or subqueries) if we know the depth or if we don't know if it is possible so how can we get the top most parent(i.e parent_id=0) of any child if it is not possible then why this technique is so famous while it has the flaws or we have another solution for this? . i have added the sql fiddle but it only has the schema
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 *?
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.
87830
Why is using '*' to build a view bad ?  Suppose that you have a complex join and all fields may be used somewhere.  Then you just have to chose fields needed.  SELECT field1, field2 FROM aview WHERE ...   The view "aview" could be SELECT table1.*, table2.* ... FROM table1 INNER JOIN table2 ...  We have a problem if 2 fields have the same name in table1 and table2.  Is this only the reason why using '*' in a view is bad?  With '*', you may use the view in a different context because the information is there.  What am I missing ?  Regards
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
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.
58842
Scenario:  Let's say I have two tables, TableA and TableB.  TableB's primary key is a single column (BId), and is a foreign key column in TableA.  In my situation, I want to remove all rows in TableA that are linked with specific rows in TableB: Can I do that through joins? Delete all rows that are pulled in from the joins?  DELETE FROM TableA  FROM    TableA a    INNER JOIN TableB b       ON b.BId = a.BId       AND [my filter condition]   Or am I forced to do this:  DELETE FROM TableA WHERE    BId IN (SELECT BId FROM TableB WHERE [my filter condition])   The reason I ask is it seems to me that the first option would be much more effecient when dealing with larger tables.  Thanks!
66119
I have 3 tables and I have to inner join Table A with Table B but a left outer between Table A and Table C.   Can I combine outer and inner join in the same query? I can nest queries and attain the desired result but I am unable to do both the joins in the same query. It appears that in other SQL languages, the order of joining is important. Is this the case in SQL Server as well?    Ok, here's the scenario.  Consider 3 tables. Table A, Table F, Table D.  I will need the recordset to contain all rows in D irrespective of whether it exists in F (after it's inner joined with A). So, a outer join comes to mind. What I would need is:   First do a inner join between A and F to get a set (this may be a null set) Then do a outer join with the recordset in (1) with D
10166
What alternatives do I have to implement a union query using hibernate? I know hibernate does not support union queries at the moment, right now the only way I see to make a union is to use a view table.  The other option is to use plain jdbc, but this way I would loose all my example/criteria queries goodies, as well as the hibernate mapping validation that hibernate performs against the tables/columns.
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.
87778
I have two tables that are joined together.   A has many B  Normally you would do:   select * from a,b where b.a_id = a.id   To get all of the records from a that has a record in b.   How do I get just the records in a that does not have anything in b?
92996
So basically in SQL Server a NULL means there is no value and thus can't be compared which returns some unexpected results.  For example, the following query doesn't return rows where value IS NULL but I want it to:  SELECT * FROM table WHERE   (value != 26)   AND date IS NULL   AND last_modified &gt;= '5/21/2014'   I understand I can do the following as a workaround but seriously? Having to add parentheses and check if is null for every single field every single time I want to include it? Seems ugly, not intuitive and crazy.  SELECT * FROM table WHERE   (value != 26 OR value is null)   AND date IS NULL   AND last_modified &gt;= '5/21/2014'   I mean I know NULL is not a value and thus can't be compared but can't you infer that it is, in fact, definitely not 26? If 26 is something and NULL is nothing and nothing is not something then NULL is not 26. Seems logical to me.  Does anyone know how I can, in a cleaner way, include nulls in my results when using comparisons without having to include an explicit check each and every time. Also turning off nulls on my tables is definitely not an option.  Edit:  My real problem of why I don't want to do it the way I showed was not exposed. So here goes. I am writing a program that let's you build queries to database tables and let's the user dynamically create filters and such which essentially at the end of the day constructs a SQL statement and gets the results to display to the user. The fields chosen by the user can be any and/or all fields in a given database and if I have to literally put that ISNULL check on every single field that would be really inefficient and make looking at the SQL super ugly. My program is table-definition-agnostic meaning I don't care what's in your table and don't want to know what your table's definition is. I just want you to pick a table, choose some fields to filter on with equals, not equals, in, not in, etc ... and then click a button to view the results of that request.
20339
the two bits of SQL below get the same result  SELECT c.name, o.product   FROM customer c, order o   WHERE c.id = o.cust_id   AND o.value = 150    SELECT c.name, o.product   FROM customer c   INNER JOIN order o on c.id = o.cust_id   WHERE o.value = 150   I've seen both styles used as standard at different companies. From what I've seen, the 2nd one is what most people recommend online. Is there any real reason for this other than style? Does using an Inner Join sometimes have better performance?  I've noticed Ingres and Oracle developers tend to use the first style, whereas Microsoft SQL Server users have tended to use the second, but that might just be a coincidence.  Thanks for any insight, I've wondered about this for a while.  Edit: I've changed the title from 'SQL Inner Join versus Cartesian Product' as I was using the incorrect terminlogy. Thanks for all the responses so far.
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?
26123
I'm trying to perform a group by action on an aliased column (example below) but can't determine the proper syntax.  SELECT       LastName + ', ' + FirstName AS 'FullName' FROM         customers GROUP BY     'FullName'   What is the correct syntax?   EDIT  Extending the question further (I had not expected the answers I had received) would the solution still apply for a CASEed aliased column?  SELECT            CASE         WHEN LastName IS NULL THEN FirstName         WHEN LastName IS NOT NULL THEN LastName + ', ' + FirstName     END AS 'FullName' FROM         customers GROUP BY          LastName, FirstName   And the answer is yes it does still apply.