| select * from one table but not all tables in a query [message #599989] |
Wed, 30 October 2013 13:56  |
 |
seahaze
Messages: 2
Registered: October 2013
Location: Boston, MA
|
Junior Member |
|
|
Hi all,
First post...and a very basic question.
How does one select * from one table without selecting * from other tables that are included in a query? For example, if in the query below I want to view all fields in some_table, but not the fields from other_table, how do it?
select *
from some_table st,
other_table ot
where st.id = ot.id
Thanks in advance!
|
|
|
|
|
|
| Re: select * from one table but not all tables in a query [message #599991 is a reply to message #599989] |
Wed, 30 October 2013 14:00  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
seahaze wrote on Thu, 31 October 2013 00:26
if in the query below I want to view all fields in some_table, but not the fields from other_table, how do it?
select *
from some_table st,
other_table ot
where st.id = ot.id
You are very close to your answer by using aliases for tables. Just mention table_alias.* in the select statement. Try and post it back here.
PS - Thanks that you posted a query as a test case in your first post, however, using code tags would have been more appreciated. In future, please follow the link that Blackswan posted.
[Updated on: Wed, 30 October 2013 14:02] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: select * from one table but not all tables in a query [message #600084 is a reply to message #600080] |
Thu, 31 October 2013 12:25 |
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
I don't see any compelling reason not to use * either.
Of course one has to use it correctly.
Consider these two approaches:
-- Vesion 1
DECLARE
v_a mytab.a%TYPE;
v_b mytab.b%TYPE;
v_c mytab.c%TYPE;
BEGIN
SELECT a, b, c
INTO v_a, v_b, v_c
FROM mytab;
END;
/
-- Vesion 2
DECLARE
l_var mytab%ROWTYPE;
BEGIN
SELECT *
INTO l_var
FROM mytab;
END;
/
Both of them are "safe" in regards to that they don't break when you change the table.
If you would use "select *" in the first case it would break when the table is changed, and if you use "SELECT a, b, c" in the second case it would break when the table is changed. So whether or not to use "select *" depends on what you do with the result of the select.
[Updated on: Thu, 31 October 2013 12:25] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: select * from one table but not all tables in a query [message #600091 is a reply to message #600087] |
Thu, 31 October 2013 12:58 |
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
BlackSwan wrote on Thu, 31 October 2013 18:38down stream code never expects the new column
It might at some point. For example I have written interface procedures that move data from system A to system B ten years ago that still work without a single change, and will continue to work as long as new columns are added in the target interface table before they are added in the source interface table.
If I had "enumerated" the columns instead of using "select *" I would have had to change that procedures 3-4 times a year to add new columns.
[Updated on: Thu, 31 October 2013 12:59] Report message to a moderator |
|
|
|
| Re: select * from one table but not all tables in a query [message #600099 is a reply to message #600087] |
Thu, 31 October 2013 14:50 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
BlackSwan wrote on Thu, 31 October 2013 13:38
down stream code never expects the new column so, old/existing code continues to work as previous.
new column implies new requirement & new code to utilize the new column; but does not break existing functionality.
Exactly! So if I use * this code has a chance to break at compile time (if, for example, code uses SELECT INTO) if I missed to update it after adding a column. And if I missed the same code but was using column list I will only find out about it when customer calls me with "app doesn't work". And yes, I can describe many scenarios where using * creates an issue. As I said, six or half-a-dozen.
SY.
|
|
|
|
| Re: select * from one table but not all tables in a query [message #600101 is a reply to message #599993] |
Thu, 31 October 2013 15:16 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
BlackSwan wrote on Wed, 30 October 2013 19:08It is a Bad Practice to have production SQL that does SELECT * Yes. I would say that programmers should project only the columns that they need. I have seen an application (written in Java, of course) where the programmers projected every column, sent the whole row back to the middle tier, and then used only one or two columns: a massive and unnecessary network hit for wide tables.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
