Select * from Table or Select Id,Field1, Field2, Field3 from Table - Best Practice

Select * from table OR select id,field1, field2, field3 from table - best practice?

Use select col1, col2, col3 from table instead of select * from table1. This has numerous advantages, as mentioned here and here.

Also see:
http://weblogs.sqlteam.com/jeffs/jeffs/archive/2007/07/26/60271.aspx

Is there a difference between Select * and Select [list each col]

Is syntax like select field1,field2,field3 from A where (a,b) in (select a,b from B) supported in derby jdbc?

no

     (THREADID,THREADID2) IN  

not working

you must seperate like

     WHERE THREADID IN ('your condition') OR THREADID2 IN ('your condition')

select * from table vs select colA, colB, etc. from table interesting behaviour in SQL Server 2005

sp_refreshview to fix the view, or use WITH SCHEMABINDING in the view definition

If a view is not created with the
SCHEMABINDING clause, sp_refreshview
should be run when changes are made to
the objects underlying the view that
affect the definition of the view.
Otherwise, the view might produce
unexpected results when it is queried.

Is there a difference between Select * and Select [list each col]

Generally, it's better to be explicit, so Select col1, col2 from Table is better. The reason being that at some point, an extra column may be added to that table, and would cause unneeded data to be brought back from the query.

This isn't a hard and fast rule though.

Does it matter which table to select FROM and which to JOIN

There is a no functional difference for inner joins, but there is a functional difference for outer joins. (Does the join order matter in SQL?)

^^ A good way of picturing why is this: a left outer join between A and B is the same thing as a right outer join between B and A. Direction is very important to outer joins. In fact that is why you don't have to say the keyword 'outer' in 'left join' or 'right join', the database knows you want an outer join because otherwise there would be no point in specifying direction.

If your question is about performance, yes, there can potentially be an advantage to specifying smaller tables first, and subsequently joining into larger tables (rather than the other way around). However it's not always possible to optimize that way depending on how the tables are related.

Oracle for instance has the LEADING hint which can be used to instruct the database to start with a specified table first even though it's not the first listed in the FROM clause (http://docs.oracle.com/cd/B13789_01/server.101/b10752/hintsref.htm#30459).

MySQL join / select on a table with transposed columns

You need to JOIN to the tbl_names table 3 times, once each to get the name for each field:

SELECT m.id, 
       n1.name AS field1,
       n2.name AS field2,
       n3.name AS field3
FROM tbl_material m
JOIN tbl_names n1 ON n1.id = m.field1
JOIN tbl_names n2 ON n2.id = m.field2
JOIN tbl_names n3 ON n3.id = m.field3
ORDER BY m.id

Output:

id  field1  field2  field3
1   Name1   Name2   Name3
2   Name3   Name2   Name1

Demo on dbfiddle

How to use GROUP_CONCAT in mySQL when one of the fields contain comma seperated numbers?

Use FIND_IN_SET() and GROUP BY B.ID, B.FIELD2

SELECT B.ID, GROUP_CONCAT(A.FIELD2 ORDER BY A.FIELD1) AS VEHICALS
FROM B INNER JOIN A 
ON FIND_IN_SET(A.FIELD1, B.FIELD2)
WHERE B.FIELD_X > 20
GROUP BY B.ID, B.FIELD2

Since the ids are guaranteed to exist in Table A there is no need for a LEFT join, use an INNER join.

See the demo.

Joining tables based on the maximum id

You can add a derived table to reduce the matching rows in TABLE3 to one per group. Another method would use a window function but you asked for a JOIN only

SELECT
    table1.field1, table1.field2, table1.field3,
    table2.field1, table2.field2, table2.field3,
    table3.field1, table3.field2, table3.field3,
    table4.field1, table4.field2, table4.field3
FROM table1
INNER JOIN table2 ON
    table1.field1 = table2.field1
    AND table1.field2 = table2.field2
    AND table2.field3 < 0
INNER JOIN table3 ON
    table2.field1 = table3.field1
    AND table2.field4 = table3.field4

--here is the added derived table. Change column names as needed
INNER JOIN (select UID, ID = max(ID) from Table3 group by UID) x
    on x.UID = table3.UID and x.mx = table3.ID

INNER JOIN table4 ON
    table1.field1 = table4.field1
    AND table1.field2 = table4.field2

Or, perhaps... something like below. It really depends on your schema and that's hard to understand with the sample data.

INNER JOIN (select field1, field4, mx = max(ID) from Table3 group by field1, field4) x
    on x.field1 = table3.field1 and x.field4 = table3.field4 and x.mx = table3.ID

Here is an example. You'll notice that the last three column pairs are identical. You only want the last one, which is the max(id) for that grouping. What ever makes a row unique in relation to the rest of your data (not your primary key, but what you are joining with) is what you'd want to include int he derived table and join condition.

declare @table table (id int identity(1,1), f1 char(1), f2 char(1))
insert into @table
values
('a','b'),
('a','c'),
('a','a'),
('b','b'),
('b','b'),
('b','b')

select * from @table

select t1.*
from @table t1
inner join 
    (select f1, f2, mx = max(id) from @table group by f1, f2) t2 on
    t1.f1 = t2.f1
    and t1.f2 = t2.f2
    and t1.id = t2.mx

How to select all records from one table that do not exist in another table?

SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL

Q: What is happening here?

A: Conceptually, we select all rows from table1 and for each row we attempt to find a row in table2 with the same value for the name column. If there is no such row, we just leave the table2 portion of our result empty for that row. Then we constrain our selection by picking only those rows in the result where the matching row does not exist. Finally, We ignore all fields from our result except for the name column (the one we are sure that exists, from table1).

While it may not be the most performant method possible in all cases, it should work in basically every database engine ever that attempts to implement ANSI 92 SQL

---

Related Topics