SELECT * from table EXCEPT these columns - this command does not exist.

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

First, the answer is no, SQL does not support that.  You must specify what you want.

Second, if you ask me "why not? It would be great!", let me ask you this in return:

"If SQL did support that syntax, and you executed

SELECT * minus ColumnX
FROM Table

but columnX did not exist in that table, what should happen?"

I think it's kind of an interesting thing to think about.    Should an error occur?  Or should it just happily return the results, since we didn't want ColumnX anyway?  Maybe it should issue a warning? I really don't know ... Would we all ever be able to agree on a definitive, logical way to handle this?

Here's another way to think about it:  What if there were a command called "DontExec" that simply didn't execute the stored procedure specified.  What should happen if you call DontExec on a stored procedure that doesn't exist?  

That sounds kind of silly, I know, but it is the same basic thing to consider as if you had a "* minus Column" option in a SELECT statement.  Shorter to type? Sure.  Does it make any sense? Not really.

By the way -- the third part of my response is usually this:  the easiest solution is to simply use SSMS, QA, or EM to assist you in building your column list by using the scripting features or the query builder if you don't want to type it all out.  

(UPDATE: As Denis points out in the comments, it's actually even easier.  Now you really have no excuse to use *. )



2
sql query to select  columns from two tables in join
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/39eb0314-4c2f-4e07-84c8-e832499049f8

How to select all columns except one column from a table ?

Naming the columns one by one in the select list should be the prefered way to select data. It makes reading as well as error searching much more easier. There is no eay to exclude one column from the * selection.





3
http://stackoverflow.com/questions/431391/php-mysql-how-to-resolve-ambiguous-column-names-in-join-operation

PHP & MYSQL: How to resolve ambiguous column names in JOIN operation?

You can set aliases for the columns that you are selecting:

$query = 'SELECT news.id AS newsId, user.id AS userId, [OTHER FIELDS HERE] FROM news JOIN users ON news.user = user.id'

question:

I have two tables in my database:

NEWS ('id' - the news id, 'user' - the user id of the author)

USERS ('id' - the user id)

I want to make a SELECT * FROM news JOIN users ON news.user = user.id, now when I get the results in PHP it's something like:

$row = mysql_fetch_array($result), and get column names by $row['column-name']... how do I get the news ID and the user ID, having the same column name?

UPDATE: Thanks everybody for the quick answers. Aliases seem the best solution.

4 join step-by-step primer
http://www.javahotchocolate.com/tutorials/join-sql.html

JOIN methodology

This is the candy store of this document: its whole contribution.

Here's how to think about things as you code a JOIN. It's a great idea to compose your JOIN in the SQL edit window of a good tool such as SQuirreL. You should invoke the SQL command at each incremental step to ensure that your statement is working (rather than wait until the end to see the whole thing fail without always realizing where it went wrong early on).

The following is illustrated using the data set developed in the example above.

1. Begin with SELECT *...
 
2. Pick the most primary table (most central, etc.) to start with: acct

   
   SELECT * FROM acct;
   
   

 
3. Join the table from which you want data to come: acct_info which yields first and last names. Begin giving the tables small reference names.

   
   SELECT * FROM acct AS a
       INNER JOIN acct_info AS ai;
   
   

 
4. Compose the ON clause from the two columns (one from each of the two tables) that intersect: acct_info.acct_oid = acct.oid

   
   SELECT * FROM acct AS a
       INNER JOIN acct_info AS ai ON ai.acct_oid = a.oid;
   
   

    
5. Stack up successively JOIN'd tables according to the two preceding steps.

SELECT * FROM acct AS a
    INNER JOIN acct_info AS ai ON ai.acct_oid = a.oid
    INNER JOIN affiliations AS af ON af.acct_oid = a.oid;

 
6. Until you've finally JOIN'd all the tables whose fields you need to get the result.

SELECT * FROM acct AS a
    INNER JOIN acct_info AS ai ON ai.acct_oid = a.oid
    INNER JOIN affiliations AS af ON af.acct_oid = a.oid
    INNER JOIN affiliates AS at ON a.oid = af.affiliate_oid;

 
7. Begin paring down to the desired result by changing out the '*' in the SELECT to just the list of columns whose data you want to display.

   
   SELECT a.oid,ai.first,ai.last,at.name FROM acct AS a
       INNER JOIN acct_info AS ai ON ai.acct_oid = a.oid
       INNER JOIN affiliations AS af ON af.acct_oid = a.oid
       INNER JOIN affiliates AS at ON a.oid = af.affiliate_oid;
   
   

More comments on forgotten data rows, optimizations, other speed issues

If you know your data, for example these tables, you know that each account has an affiliation. However, if an account did not have an affiliation, as the "Jay Johnson" account we added for this example did not, then using INNER JOIN would fail to list that account in the result. Use a LEFT JOIN to solve this.

Strangely (perhaps), just using a LEFT JOIN on affiliations won't do the trick because affiliates also won't supply a row in that case (even though the "problem" is between affiliations and acct). So, we have to use LEFT JOIN with affiliatesas well.

Pinch it off early...

Note that by adding the AND below, as high in the statement as we can, will speed things up because the virtual table produced by the first JOIN will be much smaller. Smaller table, less data, faster running.

SELECT a.oid,ai.first,ai.last,at.name FROM acct AS a
    INNER JOIN acct_info AS ai ON ai.acct_oid = a.oid AND ai.first = 'Levi'
    INNER JOIN affiliations AS af ON af.acct_oid = a.oid
    INNER JOIN affiliates AS at ON a.oid = af.affiliate_oid;

LEFT JOINs are just slower...

LEFT JOINs are significantly slower than INNER JOINs. Why? INNER JOINs only produce a (virtual) table made up of matching rows (that are then filtered further by the ON clause) while LEFT [OUTER] JOINs use all of the rows in the first table in the virtual table output (before the ON clause trims them down).

5
mysql create table join column with same name
http://www.tizag.com/mysqlTutorial/mysqljoins.php