Rectangle 27 81

The asterisk character, "*", in the SELECT statement is shorthand for all the columns in the table(s) involved in the query.

The * shorthand can be slower because:

  • Not all the fields are indexed, forcing a full table scan - less efficient
  • What you save to send SELECT * over the wire risks a full table scan
  • Returning trailing columns using variable length data type can result in search overhead
  • Someone unfamiliar with the codebase would be forced to consult documentation to know what columns are being returned before being able to make competent changes. Making code more readable, minimizing the ambiguity and work necessary for people unfamiliar with the code saves more time and effort in the long run.
  • If code depends on column order, SELECT * will hide an error waiting to happen if a table had its column order changed.
  • Even if you need every column at the time the query is written, that might not be the case in the future
SELECT *
  • The purpose of the query is less obvious; the columns used by the application is opaque
  • It breaks the modularity rule about using strict typing whenever possible. Explicit is almost universally better.

It's acceptable to use SELECT * when there's the explicit need for every column in the table(s) involved, as opposed to every column that existed when the query was written. The database will internally expand the * into the complete list of columns - there's no performance difference.

Otherwise, explicitly list every column that is to be used in the query - preferably while using a table alias.

sql - Why is SELECT * considered harmful? - Stack Overflow

sql database
Rectangle 27 39

If your code depends on the columns being in a specific order, your code will break when there are changes to the table. Also, you may be fetching too much from the table when you select *, especially if there is a binary field in the table.

Just because you are using all the columns now, it doesn't mean someone else isn't going to add an extra column to the table.

It also adds overhead to the plan execution caching since it has to fetch the meta data about the table to know what columns are in *.

Good answer, but I'd change the "code will break" to "code MAY break." That's the real trouble here, the "select *" use doesn't ALWAYS produce a breaking change. And when the break does happen is usually highly decoupled from the use that ends up broken.

If someone is referencing columns ordinally in their code, they're in trouble regardless of whether they use SELECT * or not. The plan execution overhead is trivial, and wouldn't matter anyway once the plan is cached.

Then the programmer error lies in writing code that depends on the sequence of the columns. You never need to do that.

@doofledorfer - never say never. It's faster to access ordinal columns, and it is practical at times. It's a bigger error to use select * than there is to use ordinal access.

This was the first reason I thought of in answer to the question. Therefore, it MUST be the correct one.

sql - What is the reason not to use select *? - Stack Overflow

sql
Rectangle 27 6

If you are using phpMyAdmin, select a table then click the 'Export' tab. One of the output types is SQL. Selecting this will generate a SQL script to create the table and insert all of the data that is currently in the table. Make sure under the dump data option to select 'Structure and Data'

mysql - How to get query that would recreate sql table in PHPMyAdmin -...

mysql sql table recreate
Rectangle 27 11

If the column that you didn't want to select had a massive amount of data in it, and you didn't want to include it due to speed issues and you select the other columns often, I would suggest that you create a new table with the one field that you don't usually select with a key to the original table and remove the field from the original table. Join the tables when that extra field is actually required.

Select all columns except one in MySQL? - Stack Overflow

mysql select
Rectangle 27 11

If the column that you didn't want to select had a massive amount of data in it, and you didn't want to include it due to speed issues and you select the other columns often, I would suggest that you create a new table with the one field that you don't usually select with a key to the original table and remove the field from the original table. Join the tables when that extra field is actually required.

Select all columns except one in MySQL? - Stack Overflow

mysql select
Rectangle 27 11

If the column that you didn't want to select had a massive amount of data in it, and you didn't want to include it due to speed issues and you select the other columns often, I would suggest that you create a new table with the one field that you don't usually select with a key to the original table and remove the field from the original table. Join the tables when that extra field is actually required.

Select all columns except one in MySQL? - Stack Overflow

mysql select
Rectangle 27 11

If the column that you didn't want to select had a massive amount of data in it, and you didn't want to include it due to speed issues and you select the other columns often, I would suggest that you create a new table with the one field that you don't usually select with a key to the original table and remove the field from the original table. Join the tables when that extra field is actually required.

Select all columns except one in MySQL? - Stack Overflow

mysql select
Rectangle 27 11

If the column that you didn't want to select had a massive amount of data in it, and you didn't want to include it due to speed issues and you select the other columns often, I would suggest that you create a new table with the one field that you don't usually select with a key to the original table and remove the field from the original table. Join the tables when that extra field is actually required.

Select all columns except one in MySQL? - Stack Overflow

mysql select
Rectangle 27 6

Just insert the data; tables are not fundamentally ordered. The only ordering of table results occurs when you define it yourself in your select statement.

Edit: if what you want is to have a separate ordering, you would be well served by having a separate "order" column. I'd recommend making it of type float, so you can insert entries anywhere between other entries without requiring any updating. For example, if you have entry "A" with "order" column value "1", entry "B" with "order" column value "2", and you want to insert entry "C" so it shows up between entry "A" and entry "B", just insert it with the "order" column value set to 1.5 (1 + 2 / 2.0). That way, you can perform your select with an "order by" on your "order" column, and things should turn out the way you want. Floats are a reasonably good solution to this issue, since there's enough space to support a lot of "in-between" adds.

does MySQL let you insert a new record and specify the index?

@hunter: yes; in this case, "index" is your column name in the table.

+1 for mentioning that SQL tables are set-based and are not "ordered" in and of themselves. Important concept!

@djacobson, actually there is a natural order to rows in MySQL -- the insert order -- and you can reorder it with an ALTER TABLE command. But it's also true that you should think of the data as collection-based and unsorted, and not use the natural order for any purpose. @the face, yes you can do that. See the update on my answer. But also see the notes I added explaining why this should not be important to you.

@theface: with SQL, you're inserting data into a row; the order in which rows appear is ONLY determined by your SQL statement. So a "SELECT * from your_table" can technically give the rows in any order; to get them in order of ascending index value, you'd have to use something like "SELECT * from your_table ORDER BY index ASC".

sql - Is it possible to insert data into the mid section of a table us...

sql mysql database insert
Rectangle 27 6

The only way to work with the results of a stored procedure in T-SQL is to use the INSERT INTO ... EXEC syntax. That gives you the option of inserting into a temp table or a table variable and from there selecting the data you need.

That requires knowing the table definition. Not useful.

sql server - Select columns from result set of stored procedure - Stac...

sql-server tsql select stored-procedures
Rectangle 27 6

The only way to work with the results of a stored procedure in T-SQL is to use the INSERT INTO ... EXEC syntax. That gives you the option of inserting into a temp table or a table variable and from there selecting the data you need.

That requires knowing the table definition. Not useful.

sql server - Select columns from result set of stored procedure - Stac...

sql-server tsql select stored-procedures
Rectangle 27 22

One major reason is that if you ever add/remove columns from your table, any query/procedure that is making a SELECT * call will now be getting more or less columns of data than expected.

You should never write code that depends on the number of columns returned anyway.

But everyone is writing code that requires that the programmers know which data is coming back. You can't Ctrl+F your column name if it's hidden in a SELECT *.

sql - What is the reason not to use select *? - Stack Overflow

sql
Rectangle 27 3

I wanted to mention that if you use a table structure instead of div than users can hold CMD (or ALT in windows) to select a certain area of data from the table to copy. That data also pastes very easily into excel and other similar workbook programs.

css - Actual table Vs. Div table - Stack Overflow

css performance html html-table
Rectangle 27 3

I wanted to mention that if you use a table structure instead of div than users can hold CMD (or ALT in windows) to select a certain area of data from the table to copy. That data also pastes very easily into excel and other similar workbook programs.

css - Actual table Vs. Div table - Stack Overflow

css performance html html-table
Rectangle 27 1

FIrst thing you should really normalize your table and store the dates with mysql native date data-types and make sure you store them in two columns start_date and end_date. This will make your life easy.

Now coming back to the current situation there is a way you can do it, first extract the start and end date from the varchar string using substring_index function and finally use them in the condition using having clause.

select 
str_to_date(substring_index(period,'to',1),'%Y-%m-%d') as start_date,   
str_to_date(substring_index(period,'to',-1),'%Y-%m-%d') as end_date,
period 
from table_name 
having start_date >='2014-09-01' and end_date <='2014-09-30';

sql - MySQL, split column data and get result by splitted data - Stack...

mysql sql date select between
Rectangle 27 1

You have to have a FROM table and you have to match columns in order to do an INSERT INTO SELECT -

INSERT INTO `tablo` (`name1`,`tel1`) 
SELECT `name`,`tel` 
FROM `some other table`
ORDER BY `id`,`name`, `tel` DESC;

If you need data from two tables you'll have to do a JOIN or a UNION

I wouldn't have given an answer for this one (I like to know what I'm diving into and with what). OP's changed the question halfway through. Kind of like taking away the paddles midstream of a bayou, filled with crocks/gators. Ah, Louisiana; how I love thee.

php - adding two select in one insert into statement for one table - S...

php sql table select insert
Rectangle 27 1

You CAN do it, but you need to expand out your table to include by the list of possible columns that you are selecting with one row per column per source row. IF this is a large list of possibilities or a large data set....well, it ain't gonna be pretty.

With thedata as (
    select 1 row_id, 11 col1, 12 col2, 13 col3 from dual union all
    select 2 row_id, 21 col1, 22 col2, 23 col3 from dual union all
    select 3 row_id, 31 col1, 32 col2, 33 col3 from dual union all
    select 4 row_id, 41 col1, 42 col2, 43 col3 from dual )
, col_list as (
   select 1 col_id, 'col1' col from dual union all    
   select 2 col_id, 'col2' col from dual union all
   select 3 col_id, 'col3' col from dual )
select row_id, coldata
FROM  ( 
        -- here's where I have to mulitply the source data, generating one row for each possible column, and hard-coding that column to join to
        SELECT  row_id, 'col1' as col, col1 as coldata from thedata
        union all
        SELECT  row_id, 'col2' as col, col2 as coldata from thedata
        union all
        SELECT  row_id, 'col3' as col, col3 as coldata from thedata
      ) expanded_Data
JOIN col_list
  on col_list.col = expanded_data.col
where col_id = :your_id;
ROW_ID  COLDATA
1       12
2       22
3       32
4       42

So yes it can be done, but not truly dynamically as you need to be fully aware before-hand and hard-code the possible column name values that you are pulling from your table. If you need a truly dynamic select that may pick any column, or from any table, then you need to build your query dynamically and EXECUTE IMMEDIATE.

Edit - Add this caveat: I should add also that this only works if all of the possible columns grabbed are of the same datatype, or you will need to cast them all to a common data type.

sql - Use the value from a field in a table as a select statement - St...

sql oracle oracle11g oracle10g
Rectangle 27 63

select * from table order by length(column);

Documentation on the length() function, as well as all the other string functions, is available here.

MySQL - How to select data by string length - Stack Overflow

mysql select string-length
Rectangle 27 3

----Create testable
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
----INSERT INTO TestTable using SELECT
INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable

This method is used when the table is not created earlier and needs to be created when data from one table is to be inserted into the newly created table from another table. The new table is created with the same data types as selected columns.

----Create a new table and insert into table using SELECT INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable

SQL Server SELECT into existing table - Stack Overflow

sql sql-server tsql stored-procedures
Rectangle 27 85

Load data into a table in MySQL and specify columns:

LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE t1 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'  
(@col1,@col2,@col3,@col4) set name=@col4,id=@col2 ;

@col1,2,3,4 are variables to hold the csv file columns (assume 4 ) name,id are table columns.

ERROR 1148 (42000): The used command is not allowed with this MySQL version

What if i have 100 columns and I just want to import 2 columns, then should I write (@col1, @col2, ...@col100) set name=@col4, id-@col2; or there is an easy way?

How to insert selected columns from a CSV file to a MySQL database usi...

mysql csv