Joining two tables

I have two tables:
1. Incomes
Income Amount
Salary 10000
Sales 55000
and 2. Expenses

Expense Amount
House Rent 3000
Tution 4000
Fooding 4000
Travelling 2000

I want to join these to tables to form:
Salary 10000 Expense Amount
Sales 55000 House Rent 5000
(null) (null) Tution 4000
(null) (null) Fooding 4000
(null) (null) Travelling 2000

How can it be done. Thanksit cannot be done unless you specify some way of joining them

in other words, what conditions would determine that

-- Salary 10000 is matched with Expense Amount
(seems to be a typo here -- Expense and Amount are column names, not column values)
-- Sales 55000 is matched with House Rent 5000

are you planning any primary keys for these tables?

rudy
http://r937.com/|||There are a couple of ways you can handle this:

1.
select income, amount, 'income' as type from incomes
union
select expense, amount, 'expense' as type from expenses
order by type

2.
select income, amount, '','' from incomes
union
select '','',expense, amount from expenses

Or another variation.

Joing tables from different databases - performance issues

Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
MerlinIt is really opene-end question , because only you do know about your tables
structure, indexes and amount of data.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on the same
> physical machine
> 4. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on different
> physical machines and these machines contact each other through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
> if (and how big) are there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||1 is same as 2. The optimizer has all the information and can process the qu
ery the same whether the
tables are in the same database or different database. For 3 and 4, the quer
y is optimized locally
and parts of the query is passed onto the linked server. This limits the fle
xibility that the
optimizer otherwise has. 4 is obviously worse than 3. For quantification, yo
u need to test with your
data, schema, queries etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL Serve
r (linked servers) when
> these instances are located on the same physical machine
> 4. from different databases located on different instances of MS SQL Serve
r (linked servers) when
> these instances are located on different physical machines and these machi
nes contact each other
> through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
if (and how big) are
> there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||
> It is really opene-end question , because only you do know about your tabl
es
> structure, indexes and amount of data.
Structure of these tables is the same in all variants.
I don't expect exact answers, because it is impossible without exact
info, but I think it is possible to point at mainspriongs which affect
performance.
Difference between variants 1 and 4 is obvious.
What about difference between 1 and 2, 2 and 3 it's not obvious for me.
Merlin|||U?ytkownik Tibor Karaszi napisa?:

> 1 is same as 2. The optimizer has all the information and can process
> the query the same whether the tables are in the same database or
> different database. For 3 and 4, the query is optimized locally and
> parts of the query is passed onto the linked server. This limits the
> flexibility that the optimizer otherwise has. 4 is obviously worse than
> 3. For quantification, you need to test with your data, schema, queries
> etc.
Thanks, this is the info what I've expcected.
Do you have feeling what difference can be between variants 2 and 3
(small, medium, big)
Merlin|||> Do you have feeling what difference can be between variants 2 and 3 (small
, medium, big)
I'd say medium to big. But you can always find exceptions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:43A160FA.6060206@.NOSPAM_poczta.onet.pl...
> U?ytkownik Tibor Karaszi napisa?:
>
> Thanks, this is the info what I've expcected.
> Do you have feeling what difference can be between variants 2 and 3 (small
, medium, big)
> Merlin
>

Joing tables from different databases - performance issues

Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
Merlin
That really depends on a lot of things such as the queries
themselves and on your network configuration - issues such
as are the servers on the same switch. There is no black and
white percentage to give you. If you are concerned about
performance across servers, you may want to check the
performance tuning tips in the following article:
http://www.sql-server-performance.com/linked_server.asp
-Sue
On Thu, 15 Dec 2005 13:00:15 +0100, MerlinXP
<MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote:

>Hello,
>
>What is performance difference between joining tables
>1. from the same database
>2. from different databases located on the same instance of MS SQL Server
>3. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on the same
>physical machine
>4. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on different
>physical machines and these machines contact each other through LAN
>I guess, performance or variant 1 is the best and for 4 is the worst,
>but if (and how big) are there differences between: 1 and 2, 2 and 3.
>
>Thanks a lot.
>Merlin

Joing tables from different databases - performance issues

Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
Merlin
It is really opene-end question , because only you do know about your tables
structure, indexes and amount of data.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on the same
> physical machine
> 4. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on different
> physical machines and these machines contact each other through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
> if (and how big) are there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin
|||1 is same as 2. The optimizer has all the information and can process the query the same whether the
tables are in the same database or different database. For 3 and 4, the query is optimized locally
and parts of the query is passed onto the linked server. This limits the flexibility that the
optimizer otherwise has. 4 is obviously worse than 3. For quantification, you need to test with your
data, schema, queries etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on the same physical machine
> 4. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on different physical machines and these machines contact each other
> through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but if (and how big) are
> there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin
|||
> It is really opene-end question , because only you do know about your tables
> structure, indexes and amount of data.
Structure of these tables is the same in all variants.
I don't expect exact answers, because it is impossible without exact
info, but I think it is possible to point at mainspriongs which affect
performance.
Difference between variants 1 and 4 is obvious.
What about difference between 1 and 2, 2 and 3 it's not obvious for me.
Merlin
|||U?ytkownik Tibor Karaszi napisa?:

> 1 is same as 2. The optimizer has all the information and can process
> the query the same whether the tables are in the same database or
> different database. For 3 and 4, the query is optimized locally and
> parts of the query is passed onto the linked server. This limits the
> flexibility that the optimizer otherwise has. 4 is obviously worse than
> 3. For quantification, you need to test with your data, schema, queries
> etc.
Thanks, this is the info what I've expcected.
Do you have feeling what difference can be between variants 2 and 3
(small, medium, big)
Merlin
|||> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
I'd say medium to big. But you can always find exceptions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:43A160FA.6060206@.NOSPAM_poczta.onet.pl...
> U?ytkownik Tibor Karaszi napisa?:
>
> Thanks, this is the info what I've expcected.
> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
> Merlin
>

Joing tables from different databases - performance issues

Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
MerlinThat really depends on a lot of things such as the queries
themselves and on your network configuration - issues such
as are the servers on the same switch. There is no black and
white percentage to give you. If you are concerned about
performance across servers, you may want to check the
performance tuning tips in the following article:
http://www.sql-server-performance.com/linked_server.asp
-Sue
On Thu, 15 Dec 2005 13:00:15 +0100, MerlinXP
<MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote:

>Hello,
>
>What is performance difference between joining tables
>1. from the same database
>2. from different databases located on the same instance of MS SQL Server
>3. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on the same
>physical machine
>4. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on different
>physical machines and these machines contact each other through LAN
>I guess, performance or variant 1 is the best and for 4 is the worst,
>but if (and how big) are there differences between: 1 and 2, 2 and 3.
>
>Thanks a lot.
>Merlin