"inner SELECT"

Hi everybody!
I'm brand new to MS SQL Server,
Can you give me some examples of "Inner SELECT"?
Regards,
alias 939Nope.
Select
or
Inner join

No such thing as inner select.

It's covered well with examples in bol.

select a.col1, b.col2
from tbl1 a
inner join tbl2 b
on a.pk = b.fk|||SELECT will retireve your data from the DB. The effective format is "SELECT <What - Field Name or Names separated by comma> FROM <Which DB/Table> WHERE <Conditions for filtering results, such as particular field = something> ORDER BY <How you want it sorted when it;s output>"

On the other hand, JOIN is used when you need to work with data across two or more tables. The effective format is "SELECT <What> FROM <First DB/Table Name> JOIN <Second DB/Table Name> ON <Condition that links the first and second table together> WHERE <Condition for filtering the results> ORDER BY <Sorting order>

For example, I have one table called EMPLOYEES that holds ID, NAME, POSITION_ID and another table called POSITIONS that holds ID, POSITION_NAME. Now, the POSITION_ID in the EMPLOYEES table refers to the ID in the POSITIONS table. So, to get the list of all employee names and their positions, I would write:

SELECT NAME, POSITION_NAME
FROM EMPLOYEES
JOIN POSITIONS ON EMPLOYEES.POSITIONID = POSITIONS.ID
ORDER BY NAME, POSITION_NAME

Notice I have skipped WHERE altogether (WHERE and ORDER BY are optional. Ther are plenty more options, see help)

Also notice my SELECT statement contains field names from both tables - possible thanks to JOIN-ing them in the third row.

Note also that if field names repeat in the joined tables you need to clarify your reference. For example, I could have written the above as:

SELECT EMPLOYEES.NAME, POSITIONS.POSITION_NAME
FROM ...

Of course, this could be tedious, right? So what you can do is to assign alias to the tables / databases you deal with. In the above example:

SELECT E.NAME, P.POSITION_NAME
FROM EMPLOYEES E
JOIN POSITIONS P ON E.POSITIONID = P.ID
ORDER BY NAME, POSITION_NAME

Notice how I put EMPLOYEES E and POSITIONS P in the above command. Effectively I assigned "E" to be alias for EMPLOYEES and "P" to be alias for POSITIONS, and so in the futre I only need to refer to these tables via this alias.

Finally, to make the thing complete, you can have more than one type of JOIN - INNER JOIN, LEFT JOIN, RIGHT JOIN and OUTER JOIN. I'll let you browse through help for these.

Hope this helps.

Cheers,

V

Originally posted by thebitwriter
Hi everybody!

I'm brand new to MS SQL Server,

Can you give me some examples of "Inner SELECT"?

Regards,
alias 939